Forum Discussion

JGDANIEL's avatar
JGDANIEL
Copper Contributor
Mar 08, 2020

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 this problem difficult:

 

  • Some pupils have NA as their score e.g. because they were absent during the test.
  • The formulas need to update automatically if pupils (rows) are added or removed.
  • Each third should contain only Non-NA pupils.
  • Each third should ideally contain an equal number of pupils, however, additional pupils should be placed in the Top, then Middle e.g. if there are 11 Non-NA pupils, there should be 4 in Top, 4 in Middle and 3 in Bottom.
  • The formulas need to be easily copied so that they automatically work beneath a new subject if one is added.

 

If you are up for a challenge/think you might be able to help then please read on...

 

If you take a look at the spreadsheet attached, I have managed to generate long and confusing but working formulas for some of the cells already. They have been written so that they can be easily copied to other parts of the spreadsheet for automatic calculation. The initial 'Count' relies on the need for an empty row between the bottom of the data and the start of the statistics. It looks at all rows between row 2 and 36 (maximum number of pupils in a class) and seeks out the first empty row in order to calculate how many pupils there are listed.

 

Any help/ideas to do this completely differently are gratefully received. Good luck!

 

Thanks,

Jonathan

excel

2 Replies

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Mar 10, 2020

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

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 10, 2020

    JGDANIEL I suggest you introduce some helper formulae below your statistics. It makes maintaining and expanding the schedule relatively easy and you avoid long and confusing formulae. If you want to add a pupil, first insert a row above thet stats. Then just start typing directly below the last pupil. The table will expand automatically and the so will the calculations in the cells below. Adding a subject works similar. Just start typing directly outside the table on the right hand side and the table will automatically extend. Then, drag the stats and the helper formulae below to the next column and all should update instantly.

     

    Now, the helper formulae that I am referencing in the stats are dynamic array formulae. If you are on an older Excel version that does not recognise these, you can use the formulae from columns E,F and G.

Resources