# Calculate % from 2 count values in Pivot Chart

#### ar0sa

##### New Member
Greetings all,

I have the following pivot table;

 Row Lables Count of Eligible Count of Enrolled Manual Calculation (Enrolled/Eligible)* a 6 4 66% b 4 1 25% d 3 2 66% Grand Total 13 7

<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:

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

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

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.

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.

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!

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

Replies
6
Views
163
Replies
1
Views
440
Replies
6
Views
533
Replies
0
Views
467
Replies
1
Views
376

1,217,677
Messages
6,137,936
Members
450,099
Latest member
Pushbutton

### 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.

### Which adblocker are you using?

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

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