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

  • Thread starter Thread starter Thomas Burns
  • Start date Start date
T

Thomas Burns

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.
 
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.

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

Workaround 2: Scale the values. Let's say the data is in A1:A20. In B1 enter =A1- 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 =(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:
 McCullough, B.D. andWilson, B. (1999) On the accuracy of statistical
procedures in Microsoft Excel 97, Computational Statistics and Data

Analysis, 31, 27.37.

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

software. The American Statistician 52, 358.366.

 Kn¨ usel, L. (1998) On the accuracy of statistical distributions in

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

375.377.

 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).

 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
 
Back
Top