Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Standard deviation calculation error...
Hi,
i was reviewing my preset worksheet with the standard deviation function and noticed that the out put result contains error. Formula "=STDEV(K12:K111)" was set to to calculate standard deviation between 0-100 sets of values in the column, where values can be whole numbers or fractions, in respective columns. Formula was observed performing perfectly with whole numbers, however, with fractions (or numbers with decimal places), the output is invalid (in some sense). You may test, with 60 sets of values "1" in a column, the STDEV is "0". However, when applying the same formula to a fraction value "0.05", 60 sets in a column, the STDEV is "4.9E-17". This is definitely a NO NO answer in mathematics point of view. If you breakdown the calculation steps of a STDEV formula "s2 = (ˆ‘(x-m)^2)/N" into multiple columns, it would be identified that the culprit lies on the "ˆ‘(X-M)^2" formula, where x is the individual value and m is the mean. Example below demonstrates the breakdown of the column calculations: x m N ˆ‘(x-m)2 s2 s 0.05 0.05 1 0 0 0 0.05 0.05 2 0 0 0 0.05 0.05 3 1.44E-34 4.81E-35 6.94E-18 0.05 0.05 4 0 0 0 0.05 0.05 5 0 0 0 0.05 0.05 6 2.89E-34 4.81E-35 6.94E-18 0.05 0.05 7 3.37E-34 4.81E-35 6.94E-18 : : 0.05 0.05 58 1.01E-31 1.73E-33 4.16E-17 0.05 0.05 59 1.02E-31 1.73E-33 4.16E-17 0.05 0.05 60 1.42E-31 2.36E-33 4.86E-17 *Note that the ˆ‘(x-m)2 is calculated using array formula "{=SUM(($A$2:A61-B61)^2)}", since mean is constantly changing. Is this due to limitation of the excel formula (STDEV only works for whole numbers)? Or is there any patch for this error? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Standard deviation calculation | Excel Worksheet Functions | |||
calculation of standard deviation is not as help document says | Excel Discussion (Misc queries) | |||
Standard Deviation | Excel Worksheet Functions | |||
Standard Deviation | Excel Worksheet Functions | |||
Is there a standard deviation calculation in Excel? | Excel Worksheet Functions |