Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 460K Members
- 9,309 Online
- 558K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Pivot Table StDev calculates different value then the STDEV formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Pivot Table StDev calculates different value then the STDEV formula

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2019 06:52 AM

Hi,

I'm using Excel 2013 15.0.5153.1000 32-Bit and a pivot table to calculate StDev and compare quickly to the StDev calculated by the excel formulas. I noticed that sometimes the Pivot Table StDev function gives a different value compared to the StDev calculated by the excel formula.

When i found this i compared the StDev of the pivot table with every existing stdev formula from excel to see if i can get a match with one of the 6 formulas. In the attached file you can see this comparison with both StdDev and StdDevp function of the pivot table.

12 times out of 50 the StDev doesn't match. The error is very small, only the last few decimals are different.

Column J contains the values StdDev and StdDevp calculated by the pivot table, in columns K:P you can see the values calculated by the excel formulas. On rows 8, 10, 15, 16, 24 and 26 you can see that the StDev value from the pivot table doesn't match with any value calculated by the excel formulas. (Same thing for the StdDevp only the row numbers are different).

I'm curious if anybody knows the reason for this. Did anybody met this issue before? I can't find anything on the internet regarding this.

Labels:

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2019 02:19 AM

I assume this is caused by the algorithm of both STEDV calculations being slightly different hence causing a very small difference likely caused by rounding.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2019 03:13 AM

I'm assuming the same thing, I've checked many settings and didn't found anything that could affect the StDev calculated by the pivot table. In the attached excel file i also set up the experiment so that i have the same amount of samples (30 samples) for each "TestCodeDescription". I though that maybe this is the cause, but it's not

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2019 09:21 AM - edited 10-22-2019 09:25 AM

The magnitude of the errors is roughly what I would expect, bearing in mind the subtraction that will lose you 1 decimal place and the summation over 30 values that will go a long way to losing a second. I tried two variations of the formula for a sample standard deviation from first principles and got results that varied slightly

**= SQRT( SUM( (x - x̅)^2 ) / (n-1) )** *= 0.2555612135223550*

**= SQRT( ( SUM(x^2) - n * x̅^2 ) / (n-1) )** *= 0.2555612135223660*

Form my sample of 1 test case, the first formula matches Excel and the second matches PQ, where **x** was defined to be $B$662:$B$691 and **x̅ **

**= SUM(x) / n**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-23-2019 11:37 PM - edited 10-24-2019 12:27 AM

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

Related Conversations

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
30.9K
Views

14 Likes

14 Replies

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
35K
Views

7 Likes

35 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
179K
Views

8 Likes

29 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16.1K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft