Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I had to create a multi-level excel report which had both totals and averages at each break.
My original thought was to create two reports which would have been time-consuming, difficult to use and “quick and dirty but mostly dirty” solution. I proceeded to create the totals using the division technique mentioned in this post.
I first attempted the averages in a similar manner but the math didn’t work out because the subtotals were included. I needed a way to only take the detail lines. In the screen shot below, this would be the <Name> lines.
The solution I devised was to add column D and use width property to effectively hide the column.
Listed are the formulas and their function
D8 COUNT(E6:E7) Counts the detail lines for each manager
D9 -1 Flag to exclude lower level subtotals from the level above
(e.g. exclude manager totals from the region totals)
D12 SUMIF(D5:D11,”>0”) Totals the counts from the manager levels (exclude -1 rows)
D13 -1 Similar purpose as D9 except for next level up
E8 SUM(E6:E7) Sum <Name> lines for each manager
E9 E9/$D8 Average of the <Name> lines for the manager
E12 SUMIF($D5:$D11,"",E5:E11) Total of detail lines for the region (exclude manager totals)
E13 E12/$D12 Average of the <Name> lines for the region
D18 SUMIF(D2:D16,">0")/2 Total of counts from the region and manager levels
(exclude -1 rows). Requires divide by 2 because count will double
E18 SUMIF($D2:$D16,"",E2:E16) Total of detail lines for the regions
E19 E18/$D18 Average of detail lines
