Pivot tables and standard deviation bug - again

[jpdphd]

I am using Excel 2004 with OS X 10.4.7 on a Powerbook G4.
I noticed some unexpectedly small and large standard deviations coming
from a pivot table. I calculated these standard deviations with the
STDEV function and compared this with the pivot table. They are
different! Sometimes by as much as 200% different. I opened the same
spreadsheet with Excel on a PC and there was no discrepancy between the

two calculations of standard deviation.

I can send a copy of this spreadsheet to anyone who wants to follow
this up.

I posted this a few weeks ago but there was only one nibble & no follow
through. This is a very significant bug! Anyone who uses pivot tables
should be very concerned!

 

[Jim Gordon]

Hi,

Some changes were made in the way Excel calculates certain statistical
functions in Office 2004.

This article has details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;829208&Product=O

I don't think there should be a difference between the results from
STDEV within a pivot table or outside a pivot table, but it's possible
that one is using a cell function and the other is using a VBA routine.
The results could be different for each.

-Jim Gordon
Mac MVP

 

[jpdphd]

You say, "The results could be different for each". Why??? Differences
in the last decimal place or so might be tolerated. However, I'm
talking about a factor of 3 in some cases. And, I want to reiterate
that the same example will show no discrepancies when run on the
windows version of Excel.

Here is a simple example with 3 numbers:
0.47
0.48
0.48

SD by pivot table - 0.0047
SD by formula - 0.0058 (aggress with brute force calculation)
These numbers differ by 18%.

Larger differences arise when the pivot table chooses numbers according
to criteria.

I would really appreciate your looking at my example speadsheet.

 

[Jim Gordon]

Hi,

I'd be happy to give your workbook a look-over. You can figure out my
email address if you substitute "hot" for "warmer than warm" in the
reply to this message.

-Jim Gordon
Mac MVP

 

[sambone]

I use(d) pivot tables and am concerned!! I came across this thread
while on the phone with Microsoft tech service, so at least no one can
say they haven't been made aware of this problem by now. I happened
upon the same error, and did a similar diagnosis, and came to the same
conclusion. Nice description of the problem, by the way!

I love pivot tables, and have had my confidence in them shaken by this
bug. I don't write macros, however, and apparently one has to upgrade
to "Pro Support" to have a Microsoft tech try to solve the problems
with pivot tables (macros). Maybe if someone reading this thread has
such a high-level tech support subscription they could call...?

 

[jpdphd]

Sambone,

Someone from microsoft has agreed to look into this. I will let you
know what I find out.

By the way, this person tested the pivot table on the latest windows
version & found that it has the same bug! I must have been using an
older version when I tested it.

In the meantime, I'm trying to spread the word to beware of pivot
tables!

jpdphd

 

[Jessica Lambert [MSFT]]

Hi jpdphd,

Thanks for sending us the sample spreadsheet. I was able to reproduce the
problem on Mac Excel 2004 using your workbook, and also with one created
from scratch. One interesting thing I discovered was that this problem
seems to require non-integer data to happen. For example, if I have a
column containing {11, 11, 11, 110}, the pivot table calculations match the
formula calculations. However, if I try it with a column containing {1.1,
1.1, 1.1, 11} then I do start to get different results.

I've asked our development team to see if they can figure out what's
happening here.

As always, we appreciate you bringing these kinds of issues to our
attention!

--
Jessica Lambert

This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only.

. . . . .

 

[jpdphd]

Hi Jessica,

Thanks for looking into this. I've seen some examples where integer
input gives correct results, but not always. Try {40,41,40}.

jpdphd