Pivot table formula for percentage?

samst

Board Regular
Joined
Feb 12, 2003
Messages
69
Hi

In my Pivot Table I pull data from 2 columns of a spreadsheet (Total Cost and Total Price) What I'm trying to do is have the Pivot table reflect the percentage difference but I keep getting #N/A as a result.
The spreadsheet also has a column for the percentage difference but when I include this column in the Pivot table it sums the percentages rather than giving me the acurate percentage for the grand total. I tried adding the Total price field twice and then making the second a "% of" with the Total Cost field as the Base but that just returns the #N/A eror. Anyone have any thoughts?

Thanks as always!!
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
We would have an easier time assisting you if you provided some sample data, and precisely identified the needed calculation.
 

samst

Board Regular
Joined
Feb 12, 2003
Messages
69
Thanks for the reply. I'll simplify the example here, but basically

My spreadsheet looks like this
A B C D E
1 NAME Status Total Cost Total Price % Difference
2 X Complete $5 $10 50% (B2-A2)/B2)
3 Y In Progress $25 $30 16%
etc...



My Pivot Table looks like this:

Project Status Total Cost Total Price %Diff
X Complete 5 10 50
Y In Progress 25 30 16

GRAND TOTAL 30 40 66%

I can't figure out how to make the Grand Total % accurate. I don't want it to add up the percentages I want it to give me the percent of the Grand total difference between cost and price.
Thanks again!!
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Use a Calculated Field defined as...

=('Total Price'-'Total Cost')/'Total Price'

...and formatted as 0% to get...
Book1
CDEFGHIJKL
1TotalCostTotalPrice%DifferenceData
2$5$1050%NAMEStatusSumofTotalCostSumofTotalPrice%Difference
3$25$3017%XComplete51050%
4YIn_Progress253017%
5GrandTotal304025%
6
7
8
Sheet3
 

samst

Board Regular
Joined
Feb 12, 2003
Messages
69
Thanks!!
Sorry, but Can you tell me how I do that? or where I put it? I understand the formula I jest don't know where to put it in the pivot table
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
With the 'Data' PivotTable button selected choose the Insert | Calculated Field... menu command.
 

ilsem

New Member
Joined
May 26, 2009
Messages
6
HI

I have excel 2007. Please show how you would work out the % in the same way, as I tried it, and on the individual cell the data is correct but on the grand total line it shows it as zero and does not apply the formula.

I used the formula

=SUM('MAY 2009 H&B'/'MAY 2008 H&B'*100-100)

=sum(33,296,271 / 46,852,649 *100-100)

Result should be -29, but it shows zero

Please help
 

Forum statistics

Threads
1,084,855
Messages
5,380,311
Members
401,663
Latest member
SJL_815

Some videos you may like

This Week's Hot Topics

Top