Calculate % from 2 count values in Pivot Chart

ar0sa

New Member
Joined
Sep 18, 2014
Messages
5
Greetings all,

I have the following pivot table;

Row LablesCount of EligibleCount of EnrolledManual Calculation (Enrolled/Eligible)*
a6466%
b4125%
d3266%
Grand Total137

<tbody>
</tbody>
* Outside of pivot table with =GetPivotData

My first goal is I would like to calculate the % enrolled from within the pivot table - I feel like it should be simple to make these calculations, but this probably shows my unfamiliarity with Pivot Tables, because it is not simple. I would like this data within the pivot table as I need the data to be dynamic. The end goal is to view this information in a chart;


What I really want is to have a data point that shows the percentage enrolled above each series. I achieved this chart by increasing the series overlap to 100% on a clustered column chart. I feel like there may be a better way to do this, but this is all I've been able to come up with so far and I'm stuck. I'm using Excel 2013. Thanks in advance for your assistance.

Thank you,

Anthony
 
Last edited:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
you can find the 13 and the 7 automatically by searching for "Grand Total" in column A then offsetting 1 and 2

likewise you can find the 6 and the 4 for a and thus calculate the 66% - you can make another chart the same size as this one, scaled 0 to 100 to match your 0 to 7 then plot a vertical bar with no fill with its data label showing and position it exactly over the first chart which will now look like your chart but with a 66% at the top of the bar
 

Herbiec09

Active Member
Joined
Aug 29, 2006
Messages
250
To calculate %age enrolled within the pivot, create a calculated field within the pivot table.

In 2010, this would be Options--->Calculations --->Fields, Items & Sets---> Calculated Field
In 2007, this would be Options--->Tools---->Formulas--->Calculated field
 

ar0sa

New Member
Joined
Sep 18, 2014
Messages
5
I've thought that this should be the solution, but how do I format this? I've tried and then the calculated field appears as a Sum in the Values total and gives the #Div/0 error.
 

ar0sa

New Member
Joined
Sep 18, 2014
Messages
5

ADVERTISEMENT

Thanks for your reply, could I ask you to clarify a few things?

When you say "searching for 'Grand Total'" what do you mean? Also, what are you referring to in "Offsetting 1 and 2"? Thanks for your help.
 

ar0sa

New Member
Joined
Sep 18, 2014
Messages
5
Ah ha! I figured it out! The fields that the values were counting were text fields, and I was trying to do math with them! I just added a column with a "1" next to each text entry and now I'm able to work with them by this solution. Thanks, Herbiec!
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
match 5 in a range 2,3,7,5,9 will find the 5

offset from say A1 above numbers in A2 to A6

=offset($a$1,match(5,a2:a6,0),2

will find the 5 and step 2 columns to the right
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top