Average of Percentages

[rar]

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.

 

[SydneyGeek]

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

 

[gingerafro]

Book4
	A	B	C	D	E
3	Country	State	Region	Data	Total
4	A	1	East	Sum of Closed	4
5				Sum of Field1	40%
6		2	West	Sum of Closed	6
7				Sum of Field1	30%
8		3	North	Sum of Closed	8
9				Sum of Field1	27%
10		4	South	Sum of Closed	0
11				Sum of Field1	0%
12	A Sum of Closed				18
13	A Sum of Field1				30%
14	B	5	North East	Sum of Closed	10
15				Sum of Field1	20%
16			North West	Sum of Closed	2
17				Sum of Field1	50%
18	B Sum of Closed				12
19	B Sum of Field1				22%
20	Total Sum of Closed				30
21	Total Sum of Field1				26%
Sheet4

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

 

[StuLux]

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.

 

[rar]

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

 

[gingerafro]

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.

 

[rar]

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.