Forum Discussion
Top, Middle and Bottom Third Averages
Hi everyone, I'm looking to calculate the Top, Middle and Bottom Third Averages for some data e.g. pupils' scores in different subjects. However, I have a number of things to mention which makes ...
Hello JGDANIEL,
Without helper columns that could be like:
These are array formulas so press Ctrl+Shift+Enter
Bottom Third:
=AVERAGE(SMALL(IF($B$2:$B$12<>"NA",$B$2:$B$12),ROW(INDIRECT(1&":"&ROUNDDOWN(COUNT($B$2:$B$12)/3,)))))
Middle Third:
=AVERAGE(SMALL(IF($B$2:$B$12<>"NA",$B$2:$B$12),ROW(INDIRECT(ROUND(COUNT($B$2:$B$12)/3,)+1&":"&2*ROUND(COUNT($B$2:$B$12)/3,)))))
Top Third:
=AVERAGE(SMALL(IF($B$2:$B$12<>"NA",$B$2:$B$12),ROW(INDIRECT(2*ROUND(COUNT($B$2:$B$12)/3,)+1&":"&COUNT($B$2:$B$12)))))