I suspect I'm missing something obvious.
I would like to create a third column that shows the row percentage, where % = Column C / B. The percentage should automatically update based on the level of aggregation selected.
For example:
When school is the only level of aggregation, Percent Graduating should equal what's shown below:
School | Number of Students | Number Graduating | Percent Graduating |
---|---|---|---|
Smith Elementary | 100 | 70 | 70% |
Jones Middle | 500 | 100 | 20% |
When Grade is added, Percent Graduating should update to show the percent for each grade, for each school. (Again, just a simple row percentage.)
School | Grade | Number of Students | Number Graduating | Percent Graduating |
---|---|---|---|---|
Smith Elementary | Kinder | 30 | 20 | 10% |
Smith Elementary | 1st | 40 | 20 | 50% |
Smith Elementary | 2nd | 30 | 30 | 100% |
Jones Middle | 4th | etc. | ||
Jones Middle | 5th | |||
Jones Middle | 6th |
I've tried multiple ways and can't get it to calculate correctly.
Thank you.
sum([Number Graduating]) / sum([Number of Students]) should get you there--when you say you've tried multiple ways and can't get it to calculate correctly, can you specify what you've tried and where it falls short? Of course, a packaged workbook that demonstrates the problem would be ideal, or even screenshots of the results you are seeing will be helpful in obtaining the best possible help from forum members.