XL3/XL4: STDEVP Function Returns #NUM! Error Value

ID: Q96136

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
Microsoft Excel for the Macintosh, versions 3.0, 4.0
Microsoft Excel for OS/2, version 3.0

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, the STDEVP function may return a #NUM! error value instead of a 0 (zero) when all of the data items are identical.

CAUSE

Because the standard deviation is a measures of how widely values are dispersed from the average value, the expected result when all values in the population are the same is 0. However, there may be some instances, where due to a rounding error, a #NUM! error value will be returned.

WORKAROUND

To see an example of how to work around this problem, enter the following sample data in a worksheet:


   A1: .96   B1: .96
   A2: .96   B2: .96
   A3: .96   B3: .96
   A4: .96   B4: .96
   A5: .96   B5: .96

The formula, =STDEVP(A1:B5), will return the #NUM! error value. One alternative is to use the STDEV function. Using this date, STDEV will return 0 (zero) the correct result.

If the above workaround is not be a viable alternative due to differences in the two functions, break down the components of the functions as follows:

Select cell C1 and enter the following formula:

{=10*(SUM(A1:B5^2))}

Note that this formula must be entered as an array. In Microsoft Excel for Windows, press CTRL+SHIFT+ENTER; in Microsoft Excel for the Macintosh, press COMMAND+RETURN.

Select cell C2 and enter the following formula:

=SUM(A1:B5)^2

Select cell C3 and enter the following formula:

=ABS(C1-C2)

Select cell C4 and enter the following formula:

=SQRT(C3/(10^2))

The result will be zero.

STATUS

This is a known problem in the versions of Microsoft Excel listed at the beginning of this article. The problem was corrected in Microsoft Excel 5.0 or later.

MORE INFORMATION

The STDEVP function returns the standard deviation of a population given the entire population as arguments. The STDEVP function is based on the following formula:


   =SQRT(((n*(sum(x^2)))-(sum(x)^2))/n^2)

where n=number of values in the population and x=value.

When each of the x-values is the same, the formulas on either side of the minus sign, (n*(sum(x^2))) and (sum(x)^2)), should evaluate to the same value and when subtracted should equal zero. Zero divided by another number is zero and the square root of zero is also zero.

If, however, the formula for the second sum is evaluated to a minutely higher number, the result of the subtraction will be a negative number. This number divided by n^2 would also be negative and because, mathematically, the square root of a negative number is impossible, a #NUM! error value will be returned. Given a population of equal values, the rounding difference may occur in the course of squaring and dividing values. If this difference results in a negative numerator, the #NUM! error value will be returned.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 419-420
"Microsoft Excel Function Reference," version 3.0, page 229

Additional query words: 4.00a XL4 XL3


Keywords          : xlformula 
Version           : WINDOWS:3.0,4.0,4.0a; MACINTOSH:3.0,4.0; os/2:3.0
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        : kbprb

Last Reviewed: March 31, 1999