The information in this article applies to:
SYMPTOMSIn 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. CAUSEBecause 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:
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:
STATUSThis 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:
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 Additional query words: 4.00a XL4 XL3
Keywords : xlformula |
Last Reviewed: March 31, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |