Insert Calculated Field (wrong Qty*Price = wrong Amount)

[Edmund]

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

 

[Dave Peterson]

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]

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

 

[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 !

 

[Roger Govier]

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.

 

[Edmund]

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.

 

[Roger Govier]

I'm sorry you don't believe me.
That's the way it is. I don't think the answer will vary, no matter how many
times you ask the question.
Why would you want to bother to do this within the PT, when it is easy
enough to handle before the data gets pivoted?

 

[Edmund]

Roger, my actual file is a pipe-delimited text file with 32million rows of
records & is standing at 3.4Gigabyte. I'm manipulating this text file through
the combination of MS Access, MS Query & OLAP Cube before I can manipulate it
in Excel Pivot Table.

It's not that I don't know how to append an extra field (via VBA) to every
record of my 32million records but the fact that Insert Calculated Field is
returning a wrong grand total.

If any MVP or Microsoft personnel should confirm this to be the exact way
Insert Calculated Field was designed to return its grand total, then I rest
my case & proceed to resort to VBA to add in the "Amount" field.

Can any MVP pls confirm if this is the way Pivot Table Insert Calculated
Field was designed to function & deliver the grand total of 604.8, based on
my sample data?

Roger, I've always by-passed this concern by using your method of adding an
extra column of formula. I never mind when database is just some tens of
Megabytes. But to append an extra field at the end of each string of my 32
million rows of records in a pipe-delimited text file is just gonna make the
file size bloat further.

Eagerly awaiting a confirmation from any MVP or Microsoft personnel.

 

[Roger Govier]

Hi Edmund

I am an Excel MVP.
What you posted, was a sample where you used a calculated field.
The answer I gave you was correct, the Subtotal's and Grand Totals work in
the way I described.

If your Qty and Price were items in a standard set of data, then you can
create a Calculated Item within the Pivot table and the results will be
correct for both the lines and the Sub and Grand Totals.

Calculated Items work in a different way to Calculated Fields.

What you are now telling me, is that the file is quite different, is much
larger and is manipulated through OLAP cubes.

You cannot create Calculated Fields or Calculated items on Cube data.
You have measures and dimensions. Your Qty and Price will be measures, and
Value or Amount being Qty*Price would have to be a measure within the cube,
to have it shown in the PT.