Forum Discussion
Pivot Table StDev calculates different value then the STDEV formula
Hi, thanks for these tips. I double checked your calculation and I've got the same values for range $B$662:$B$691. However when i try this formula for another set of 30 values from range $B$182:$B$211 i get the following:
SQRT( SUM( (x - x̅)^2 ) / (n-1) ) = 0.2375459475749410
SQRT( ( SUM(x^2) - n * x̅^2 ) / (n-1) ) = 0.2375459475749460
The pivot table calculates 0.2375459475749490 for $B$182:$B$211 so we have no match here with either of the formulas. Interesting...
Edit: i think this calculation issue is related to https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result. Section "Precision" explains that excel has a max precision of 15 digits. Probably something else is still going on because of the of the pivot table's framework which further modifies the decimals, but i think that the issue is certainly related to the limitation explained in the article.
This answer is a bit late - but may be interesting to other readers.
Please don't put any value in those very last digits of the standard deviation. Unless you have hundreds of single measurements from the same parent population, you never know the standard deviation much better than to an error of a couple of %, mostly rather 10-25%. You'll never get into a situation where 4th digit after the decimal point is significant, let alone the 12th or 15th...