• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Columns Values In a Pivot Table As Percent of Grand Total

D

davis.brian

New Member
Hi,


I am trying to figure graduation rates for a school district. I need to find a fast, efficient, and safe way to calculate the percent of specific subgroups percent who graduated and did not based off a grand total.


Let me show you values from a hypothetical pivot table I am working with


# Students % Total # Hispanic % Hispanic

Graduate 950 95.48% 150

Female 500 50.25% 80

Male 450 45.23% 70

Did NOT Graduate 45 4.52% 30

Female 20 2.01% 10

Male 25 2.51% 20

Grand Total 995 100.00%


So no for instance I need to figure a quick way to calculate # Hispanic column values as percents of the grand total (995 students) in the % Hispanic column. I need to do this without hand calculating each value ie: # Hispanic graduates as percent =sum(150/995). I do this for many subgroups, and buildings. The table I use will have all these groups column by column in my table


A quick way to figure this out would be a huge time saver, and would assure the quality of my work.


Crossing my fingers, thanks!
 
Hi davis.brian,


Firstly welcome to the Chandoo's Forum,


You can actually do this (in Excel 2007) by:


1. Select your pivot table first.

2. Go to 'Options' Tab and then Formula.

3. Type formula as you can see in my attached file.


Here is the one (the sample file), It could have been better if you had provided a sample file.

http://dl.dropbox.com/u/60644346/davis.brian_Resolved.xlsx


Hope that it will resolve the issue.


Regards,

Faseeh
 
Faseeh,


Thank you for your response. Here is a pivot table I created that looks similar to what I am working with. Columns D, F, H, and J are the columns with values I want to turn into percents of the grand total of students - Cell B20 - 20 students. Ultimately I want to tell the percent each subgroup makes up from the total number of students in the population.


I didn't see the formula you used in the table you provided but hopefully the table I've attached below will give you a better idea of exactly what I'm looking at.


http://dl.dropbox.com/u/36235360/Grads%20vs%20Non%20Grads%20%25%20Columns%20Example%20%281%29.xlsx


Again, thank you for your time and help!
 
Hi Davis,


Here is the reworked file:

http://dl.dropbox.com/u/60644346/davis.brian_Reworked.xlsx


Important: The data in your sheet in not in the shape of 'List'. It is presented as table and that too is not much organized. Pivot Tables are more comfortable with lists. That is why didn't used your data table, rather i worked out a similar data in 'List' style. I recommend you to go for 'List' if you want to fully utilize potentiality of Pivot Tables.


If any thing goes wrong feel free to revert.


Thanks,

Faseeh
 
Thanks!


Really, I think I was making it more difficult than I needed to. Eventually, I just through my column for different subgroups into the column labels area and was easily able to calculate the values as a percent of the grand total.
 
You must log in or register to reply here.
Back
Top