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;

ChartSample.png

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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,943
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top