Average of Percentages

rar

New Member
Country State Zone Case Created Cases Closed %
A 1 East 10 4 40%
A 2 West 20 6 30%
A 3 North 30 8 27%
A 4 South 0 0 0%
B 5 North East 50 10 20%
B 5 North West 4 2 50%

For the above data the % of Cases closed needs to be reported Zone Wise, State wise and Country wise for which the following pivot was created.

Average of %
Country State Zone Total
A 1 East 40.00%
1 Total 40.00%
2 West 30.00%
2 Total 30.00%
3 North 26.67%
3 Total 26.67%
4 South 0.00%
4 Total 0.00%
A Total 24.17%
B 35.00%
Grand Total 27.78%

The Average of % was chosen for the pivot table data. It gives the % correctly at the zone level. But at the Country level eg. A, the total no. Case closed/ total no. of case created will be 18/30 which is 30%. But in the pivot is showing wrongly as 24.17%. Is there any advanced option in pivot table in excel to get this corrected without creating another pivot. Or is there any grouping function which could be added to the pivot to arrive at the correct figure. I also want to give ranks Zone wise according to their percentage, then to rank states according to their % and circles according to their %. What additions shoul i make to my data table to generate the ranks.

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try creating a calculated field to give the percentages. This should be correct for all groupings.
1. Display the Pivot Table toolbar
2. in this toolbar, select Formulas | Calculated Field
3. In the dialog that opens, you'll see 2 data entry boxes at the top. In the first, give your calculated field a name. In the second, press the = sign then double-click Cases Closed, press the / key and double-click Cases Created.
Click OK.
4. Replace your current percentage field with this one. Format as percent, and you're away (to format, right-click the field header, Field Settings, Number and choose Percent from the list on the left. Pick the # of decimal places and you're set)

Denis

Book4
ABCDE
3CountryStateRegionDataTotal
4A1EastSum of Closed4
5Sum of Field140%
62WestSum of Closed6
7Sum of Field130%
83NorthSum of Closed8
9Sum of Field127%
104SouthSum of Closed0
11Sum of Field10%
12A Sum of Closed18
13A Sum of Field130%
14B5North EastSum of Closed10
15Sum of Field120%
16North WestSum of Closed2
17Sum of Field150%
18B Sum of Closed12
19B Sum of Field122%
20Total Sum of Closed30
21Total Sum of Field126%
Sheet4

I used a calculated field. The formula for which is:
=if(cases=0,0,closed/cases)

This is a mathematical anomaly. The average of 40, 30, 26.67 and 0 IS 24.17 but the % of 18/60 is 30% - these are 2 different matematical functions and the results you are getting are correct.

The calculated field really worked. Thanks !!!!
Kindly help for the RANK part also. How is the ranking possible in the same pivot table - e.g Zone wise - Rank 1, Rank 2 under State 5 and State wise - Rank 1,2,3,4 for All states under Country A. A ranking needed heirarchy wise ???

I think you are after a sorting function. Go into each field's options and select advanced. Then choose sort descending and select the % field in the drop down box.

hope that works.

ps. I love your use of the word 'wise'. Very George Orwell.

Is it possible to use RANK formula in pivot table itself to give ranks to each group in a hierarchy. In the above example I have the % for Zones, States and Country. What i need to do is to rank the Zones within a particular State, States within a particular Country and finally Country-wise. I mean to say in the initial pivot table i should have Country's ranking, when i drill down further, I should get the State wise ranking for each Country and on the next drill down, I should get the Zones ranking within each state.

Replies
2
Views
249
Replies
2
Views
327
Replies
2
Views
140
Replies
1
Views
93
Replies
3
Views
263

1,207,421
Messages
6,078,436
Members
446,337
Latest member
nrijkers

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.

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