I created a Pivot Table using the above data in Sheet1:

Name Qty Price
Amy 1 0.1
Amy 2 0.2
Amy 3 0.3
Bob 4 0.4
Bob 5 0.5
Bob 6 0.6
Chris 7 0.7
Chris 8 0.8
Chris 9 0.9
Danny 10 1
Danny 11 1.1
Danny 12 1.2


To get Excel Pivot Table to compute a new culculated field called "Amount",
I proceeded to "Insert Calculated Field" using formula =Qty*Price. But when
this calculated field appeared in the Pivot Table, I got the below wrong
results, no matter how I tried.

My Pivot Table turned out like this (totally wrong math multiplication):

Name Data Total
Amy Sum of Qty 6
Sum of Amount 3.6
Bob Sum of Qty 15
Sum of Amount 22.5
Chris Sum of Qty 24
Sum of Amount 57.6
Danny Sum of Qty 33
Sum of Amount 108.9
Total Sum of Qty 78
Total Sum of Amount 608.4
======================================

Seems Excel multiplied the SUBTOTAL of Qty with Price, instead of muliplying
each record before summing up the results.

I tried with different machines & met the same problem too. What's gone
wrong? How to remedy?

Thanks in advance

--
Edmund
(Using Excel 2003)

Any chance you've got some hidden rows that are affecting the totals?

Maybe via data|filter|autofilter or even by manually hiding them?



Edmund wrote:

I created a Pivot Table using the above data in Sheet1:

Name Qty Price
Amy 1 0.1
Amy 2 0.2
Amy 3 0.3
Bob 4 0.4
Bob 5 0.5
Bob 6 0.6
Chris 7 0.7
Chris 8 0.8
Chris 9 0.9
Danny 10 1
Danny 11 1.1
Danny 12 1.2

To get Excel Pivot Table to compute a new culculated field called "Amount",
I proceeded to "Insert Calculated Field" using formula =Qty*Price. But when
this calculated field appeared in the Pivot Table, I got the below wrong
results, no matter how I tried.

My Pivot Table turned out like this (totally wrong math multiplication):

Name Data Total
Amy Sum of Qty 6
Sum of Amount 3.6
Bob Sum of Qty 15
Sum of Amount 22.5
Chris Sum of Qty 24
Sum of Amount 57.6
Danny Sum of Qty 33
Sum of Amount 108.9
Total Sum of Qty 78
Total Sum of Amount 608.4
======================================

Seems Excel multiplied the SUBTOTAL of Qty with Price, instead of muliplying
each record before summing up the results.

I tried with different machines & met the same problem too. What's gone
wrong? How to remedy?

Thanks in advance

--
Edmund
(Using Excel 2003)

--

Dave Peterson

Thanks for responding. No, it does not hv any hidden rows nor columns.

In my company, I've tested on different machines running Excel 2000 & 2003.
All returned wrong results. I'm really stunned.

Amy's amount supposed ((1*0.1)+(2*0.2)+(3*0.3)) = 1.4
However, Insert Calculated Field returned her amount to be 3.6

Using my sample database, can u help to verify it from your machine & see if
your Pivot Table returns the correct 'Amount' (via calculated field formula =
Qty * Price)? If your machine returns no erraneous results, then my IT
department will be in a lot of trouble.

Thanks again.

Edmund

You may download my sample file (size 16kb only) from the below link & see
for yourself.

http://www.mediafire.com/?cjjoyzwvfzx

Eagerly awaiting your response. Thanks !

--
Edmund
(Using Excel 2000 & 2003)

Hi Edmund

I'm afraid that that is the way that Calculated Fields work within Pivot
Tables.
The Sub-totals and Totals use the Sum of Qty * Sum of Price, and not
Sum(Qty*Price)

In the case of AMY, it uses sum of Amy's qty = 6 * Sum of Amy's price =0.6
to give a result of 3.6
Similarly for all of the others, till you get to Total, where sum of Qty is
78 * Sum of price 7.8 = 604.8

You need to carry out the calculation in the source table.
Remove your calculated field
In the source table, add another column (D) with =B2*C2 copied down, and
titled Amount

Add Name, Qty, Price to the Row area.
Add Amount to the Data area and you will se the correct results.
--
Regards
Roger Govier



"Edmund" wrote in message
...
You may download my sample file (size 16kb only) from the below link & see
for yourself.

http://www.mediafire.com/?cjjoyzwvfzx

Eagerly awaiting your response. Thanks !

--
Edmund
(Using Excel 2000 & 2003)

Is this really the way Insert Calculated Field been designed to behave in
Excel?

I find it difficult to swollow the grand total of 604.8, returned by Insert
Calculated Field.

I can't imagine how much tax will need to be paid for over reporting your
sales amount should any sales report be generated using Insert Calculated
Field.

Can anybody else confirm if this is really the way Excel's Insert Calculated
Field was desinged to function?

Thanks in advance.

--
Edmund
(Using Excel 2000 & 2003)