Unable to calculate STD DEV for any value with 6 or more digits to the left dec.

I am unable to obtain the standard deviation for any value 
with six or more digits to the left of the decimal.
The values are coordinates, usually with six or seven 
digits to the left of the decimal and three to the right.
Found four types of standard deviation in the Microsoft 
Excel help menus.
1) STDEV - will work with values up to five places left of 
the decimal point.
2) STDEVA - will work with values up to five places left 
of the decimal point.
3) STDEVP - will work with values up to five places left 
of the decimal point.
4) STDEVPA - will work with values up to five places left 
of the decimal point.
The STDEVP and STDEVPA give the values that are expected.

For values with six, seven, eight or nine digits to the 
left of the decimal, a wrong value or zero is obtained, 
depending on the number of values in the list.

Is there a problem with larger values?
Any help would be appreciated.
Thanks.

0
tgburns (1)
1/22/2004 2:30:45 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
358 Views

Similar Articles

[PageSpeed] 56

This is a multi-part message in MIME format.

------=_NextPart_000_005D_01C3E12D.6FCFE7A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Thomas,
This is a well known problem and it has bee so since the beginning of =
Excel. It was cured in Excel 2003. The problem has to do with the =
'one-pass' algorithm used. There is a lost of precision with large =
numbers.=20

Workaround 1: Use the formula suggest long ago by Jerry Lewis:
=3DSQRT(DEVSQ(thedata))

Workaround 2: Scale the values. Let's say the data is in A1:A20. In B1 =
enter =3DA1- MAX($A$1:$A$20), copy down to B2; compute STDEV of the data =
in column B.

Workaround 3: Do the calculation on a worksheet mimicking the way you =
would do it manually. Let's say the data is in A1:A20. Compute the =
average in say A25. In B1 use =3D(A1-$A$25)^2; copy this down to B20. In =
B25 compute SUM(B1:B20)/(COUNT(B1:B20)-1) and in B25 find SQRT of this =
value.


Some references:
=0F McCullough, B.D. andWilson, B. (1999) On the accuracy of statistical
procedures in Microsoft Excel 97, Computational Statistics and Data

Analysis, 31, 27.37.

=0F McCullough, B.D. (1999) Assessing the reliability of statistical

software. The American Statistician 52, 358.366.

=0F Kn=A8 usel, L. (1998) On the accuracy of statistical distributions =
in

Microsoft Excel 97, Computational Statistics and Data Analysis, 26,

375.377.

=0F Axford, R.L., Grunwald, G.K. and Hyndman, R.J. (1995) .The use of

information technology in the research process.. Invited chapter in

Health informatics: an overview, (ed. Hovenga, Kidd, Cesnik).

=0F Sawitzki, G. (1994) Testing numerical reliability of data analysis

systems. Computational Statistics and Data Analysis, 18, 269.286.

Best wishes
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

"Thomas Burns" <tgburns@aep.com> wrote in message =
news:1f1a01c3e0f4$522319e0$a101280a@phx.gbl...
> I am unable to obtain the standard deviation for any value=20
> with six or more digits to the left of the decimal.
> The values are coordinates, usually with six or seven=20
> digits to the left of the decimal and three to the right.
> Found four types of standard deviation in the Microsoft=20
> Excel help menus.
> 1) STDEV - will work with values up to five places left of=20
> the decimal point.
> 2) STDEVA - will work with values up to five places left=20
> of the decimal point.
> 3) STDEVP - will work with values up to five places left=20
> of the decimal point.
> 4) STDEVPA - will work with values up to five places left=20
> of the decimal point.
> The STDEVP and STDEVPA give the values that are expected.
>=20
> For values with six, seven, eight or nine digits to the=20
> left of the decimal, a wrong value or zero is obtained,=20
> depending on the number of values in the list.
>=20
> Is there a problem with larger values?
> Any help would be appreciated.
> Thanks.
> 
------=_NextPart_000_005D_01C3E12D.6FCFE7A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Hi Thomas,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>This is a well known problem and it has =
bee so=20
since the beginning of Excel. It was cured in Excel 2003. The problem =
has to do=20
with the 'one-pass' algorithm used. There is a lost of precision with =
large=20
numbers. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Workaround 1: Use the formula suggest =
long ago by=20
Jerry Lewis:<BR>=3DSQRT(DEVSQ(thedata))</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Workaround 2: Scale the values. Let's =
say the data=20
is in A1:A20. In B1 enter =3DA1- MAX($A$1:$A$20), copy down to B2; =
compute STDEV=20
of the data in column B.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Workaround 3: Do the calculation on a =
worksheet=20
mimicking the way you would do it manually. Let's say the data is in =
A1:A20.=20
Compute the average in say A25. In B1 use =3D(A1-$A$25)^2; copy this =
down to B20.=20
In B25 compute SUM(B1:B20)/(COUNT(B1:B20)-1) and in B25 find SQRT of =
this=20
value.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Some references:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>=0F McCullough, B.D. andWilson, B. =
(1999) On the=20
accuracy of statistical</FONT></DIV>
<DIV>
<P align=3Dleft><FONT face=3DArial size=3D2>procedures in Microsoft =
Excel 97,=20
Computational Statistics and Data</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>Analysis, 31, =
27.37.</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>=0F McCullough, B.D. (1999) =
Assessing the=20
reliability of statistical</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>software. The American =
Statistician 52,=20
358.366.</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>=0F Kn=A8 usel, L. (1998) On =
the accuracy of=20
statistical distributions in</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>Microsoft Excel 97, =
Computational=20
Statistics and Data Analysis, 26,</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>375.377.</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>=0F Axford, R.L., Grunwald, =
G.K. and=20
Hyndman, R.J. (1995) .The use of</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>information technology in =
the research=20
process.. Invited chapter in</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>Health informatics: an =
overview, (ed.=20
Hovenga, Kidd, Cesnik).</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>=0F Sawitzki, G. (1994) =
Testing numerical=20
reliability of data analysis</FONT></P>
<P align=3Dleft><FONT face=3DArial size=3D2>systems. Computational =
Statistics and Data=20
Analysis, 18, 269.286.</FONT></P></DIV>
<DIV><FONT face=3DArial size=3D2>Best wishes<BR>Bernard =
Liengme<BR></FONT><A=20
href=3D"http://www.stfx.ca/people/bliengme"><FONT face=3DArial=20
size=3D2>www.stfx.ca/people/bliengme</FONT></A><BR><FONT face=3DArial =
size=3D2>remove=20
CAPS in e-mail address</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>"Thomas Burns" &lt;</FONT><A=20
href=3D"mailto:tgburns@aep.com"><FONT face=3DArial=20
size=3D2>tgburns@aep.com</FONT></A><FONT face=3DArial size=3D2>&gt; =
wrote in message=20
</FONT><A href=3D"news:1f1a01c3e0f4$522319e0$a101280a@phx.gbl"><FONT =
face=3DArial=20
size=3D2>news:1f1a01c3e0f4$522319e0$a101280a@phx.gbl</FONT></A><FONT =
face=3DArial=20
size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>&gt; I am unable to =
obtain the=20
standard deviation for any value <BR>&gt; with six or more digits to the =
left of=20
the decimal.<BR>&gt; The values are coordinates, usually with six or =
seven=20
<BR>&gt; digits to the left of the decimal and three to the =
right.<BR>&gt; Found=20
four types of standard deviation in the Microsoft <BR>&gt; Excel help=20
menus.<BR>&gt; 1) STDEV - will work with values up to five places left =
of=20
<BR>&gt; the decimal point.<BR>&gt; 2) STDEVA - will work with values up =
to five=20
places left <BR>&gt; of the decimal point.<BR>&gt; 3) STDEVP - will work =
with=20
values up to five places left <BR>&gt; of the decimal point.<BR>&gt; 4) =
STDEVPA=20
- will work with values up to five places left <BR>&gt; of the decimal=20
point.<BR>&gt; The STDEVP and STDEVPA give the values that are =
expected.<BR>&gt;=20
<BR>&gt; For values with six, seven, eight or nine digits to the =
<BR>&gt; left=20
of the decimal, a wrong value or zero is obtained, <BR>&gt; depending on =
the=20
number of values in the list.<BR>&gt; <BR>&gt; Is there a problem with =
larger=20
values?<BR>&gt; Any help would be appreciated.<BR>&gt; Thanks.<BR>&gt;=20
</FONT></BODY></HTML>

------=_NextPart_000_005D_01C3E12D.6FCFE7A0--

0
bliengme5824 (3040)
1/23/2004 1:19:33 AM
Reply:

Similar Artilces:

How to Hide Left Panel of Folder Window?
I'm well behind the Windows curve and I'm just now learning to use Windows XP Home. Up to now I've been using Windows 2000 Pro. On my XP desktop I have a shortcut to my music folder which containins my MP3 files. When I double click on the shortcut, a window opens on the desktop displaying the contents of my music folder. This window has a panel along the left side containing items like "File and Folder Tasks" and "Other Places." I can't figure out how to close, hide or otherwise delete this panel. This panel takes up a lot of prec...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Why is Excel changing the last 2 digits of a 17 digit num to 00.
When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to 00 when I leave the cell. Format - Cell does not have a setting to stop this 'feature'. How do I make Excel recongize the large number? On Thu, 28 Jul 2005 19:09:01 -0700, "Allie" <Allie@discussions.microsoft.com> wrote: >When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to >00 when I leave the cell. Format - Cell does not have a setting to stop this >'feature'. Excel Specifications and Limits: Calculation specifications Feature Maximu...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

Calculate Subreport totals in a main report Group footer
Hi all Apologies if this has been answered before but I can’t find it. I have a main Report with a Group called “Product_Category” which lists a number of “Products” in the Detail I have a Subreport named “product_costs” which has a record for each date and Text Boxes named “materials” and “fuel” (there are more but I’ll keep it simple). The Subreport sums all costs and has Text Boxes named “summaterials” and “sumfuel” in the footer (all with a height of 0.1cm) The Subreport is embedded in the Detail of the Categories and linked by Product_id In the Detail of the Main Rep...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

IE 6/ActiveX assert in CTLNOWND.CPP line 305
Hello, I have a problem with an ActiveX inside a web page. A random ASSERT in CTLNOWND.CPP line 305 occours during a GetDC first call. It's seems wnd handle is still NULL (it begin valid immediatly after). If I retard calling putting a button in html page it work. CDC* COleControl::GetDC(LPCRECT lprcRect, DWORD dwFlags) { ASSERT((m_hWnd != NULL) || (m_bInPlaceSiteWndless && m_bInPlaceActive)); ..... This problem occours random (more times on faster machines and in release mode) and just on "init" time on my control. Probably my control does this operation in ...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

AutoFilter #6
Thanks for the help. I am using Excel XP. I have a list of items that come from an Oracle Database. They are vehicle numbers that begin with a 0. (028,034,039,056, etc) I have an autofilter and want to filter out numbers >= 028 and <= 039. I always get an empty set returned to me. I assume this is because these are text items rather than numbers. Is there a way to accomplish this filter with text? Hmmm, not really sure if this is what you mean but have you tried puting a ' infront of the number, it denotes it as text. (It is under the " key) >-----Original Messag...

Storing distinct values in an array
Hello .. in the speadsheet, i have a column containing a series of numbers maybe of them repeated multiple times... i would like to store all distinct values in an array any ideas on how i could do that ... eg , if these were the numbers going down column then 1 3 5 7 5 3 5 7 5 4 3 4 5 7 8 5 3 .. store 1,3,5,7,9,4,8 in an array, in no specifi order thank you shimee -- shimee ----------------------------------------------------------------------- shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showt...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

Advanced Security #6
I am setting up security access for new users using Advanced Security. I am using the by Menu view as my primary navigation tool. However, there are a number of objects within the by Menu view that I am not allowed to change. They appear with a "?" next to the description. Some of them I can locate by navigating through the by Dictionary view. However, there are several that I cannot locate (example: Tools --> Utilities --> Project --> Service Utilities --> Create Routine Master). Is there a guide that crossreferences objects between the menu and dictionary ...

Show zero values
When a formula returns a zero value, the zero value is not being displayed. I have reviewed the following: - conditional formatting - zero values are checked (Tools/Options/View) - stepped through my VB code (it inserts formulae based on a Worksheet Change event) - locked and hidden values unchecked with and without protection (this should have no effect when the sheet is unprotected) Are there any other circumstances that result in a zero value not being displayed? Are there any other reasons for this occurrence? Cheers John Check also normal cell formatting! there are formats that hide ...

Bounce Message 'Unable to relay for '
I have a user that recieved a bounce message from our Exchange 2003 server when trying to email a client. Can someone please point me in the right direction, I have never seen anything like this. I don't see why this is marked as a 'relay' message when it came directly from her outlook 2003 client (It is in her sent items) Your message did not reach some or all of the intended recipients. Subject: Donna appointment xxxxxxxxx Sent: 5/24/2005 3:39 PM The following recipient(s) could not be reached: xxxxxxxxxxx@centurybathworks.com on 5/24/2005 3:39 PM You do not have permissi...

Calculate age as of a date certain
Employee benefit enrollment requires the age of each employee as of a specific date, such as 1/1/2005. Given the date of birth, how would this be calculated? -- Joe S. Hi see: http://www.cpearson.com/excel/datedif.htm#Age -- Regards Frank Kabel Frankfurt, Germany "Joe S." <JoeS@discussions.microsoft.com> schrieb im Newsbeitrag news:0EC4F9E5-2E60-448A-A107-7B085BC764C5@microsoft.com... > Employee benefit enrollment requires the age of each employee as of a > specific date, such as 1/1/2005. Given the date of birth, how would this be > calculated? > -- > Joe ...

CButton::GetButtonStyle()
hi! is it possible that the function GetButtonStyle() returns one single style value? where can i get information when to use a mask and when to use the plain value? how about other controls/windows? actually i am a little bit confused about this... thanks, philipp "Philipp Huber" <philipp.huber@innval.com> wrote in message news:bonpkq$7hi$04$1@news.t-online.com... > hi! > > is it possible that the function GetButtonStyle() returns one single style > value? I'm not sure what you're asking, but GetButtonStyle returns the LOBYTE of the GetWindowLong/GW...

Unable to install office
Thanks in advance for any help. I recently purchased Office Ultimate 2007 Home and Student. Installation goes fine until half way through where the install wizard stops and the following error message appears: Error 25004 - Config.xml not specified. Valid config.xml file must be authored when deploying '(null)' The installer then rolls back the changes made. I have read several posts which mention this issue and have followed all advice to no effect. I have manually uninstalled all office products currently on my system to ensure there are no conflicts. Does ...

Comparing values #2
I have two sheets in a workbook. The first column in each sheet has part numbers. I would like to compare worksheet1 column a to worksheet 2 column a and identify duplicate values. I have not been able to make this work. -- Dirk Chip Pearson has lots of ways to work with duplicates/uniques at: http://www.cpearson.com/excel/duplicat.htm Dirk_Bob wrote: > > I have two sheets in a workbook. The first column in each sheet has part > numbers. > I would like to compare worksheet1 column a to worksheet 2 column a and > identify duplicate values. I have not been able to make...

SOP NonInventory Unit Cost 6 Decimal Places
Is there any simple way to increase the currency decimal places to greater than the current highest value of 5? Thanx Scott, I don't believe there is any easy or even moderately difficult way to do this, as the field types used to store amounts in GP only go up to 5 decimal places. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Scott Rutledge" <sersoar@hotmail.com> wrote in message news:E102B81B-8555-400E-843C-240C58794294@microsoft.com... > Is there any...

Assign a value to a cell
I'll apreciate your help with this in A1 i have a value in days that is used to make several calculations but I need it to change if a specific condition is given something lik this: A1=300 If b1<a1 then a1=b1 where b1 is a value calculated somewhere else. Thank you for your help. Rodolf -- Message posted from http://www.ExcelForum.com Hi try the following formula in A1: =MIN(B1,300) Frank > I'll apreciate your help with this > > in A1 i have a value in days that is used to make several > calculations, but I need it to change if a specific condition is > giv...

Cntl Key move/select to change in value
Just another convenience key sequence to make my life easier. Sure, maybe I could do it with a macro. But, it seems like a nice little obvious addition to regular functionality. Cntl-Alt down arrow moves you down to where the value changes. Cntl-alt-shift selects to change. Up, down, right, left of course. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the s...