Averaging the nth largest numbers in a list

I use Excel quite heavily - looking at cohort data, children's attainment and calculating value added scores. All pretty usual teacher stuff. Sometime I look across multiple schools and sometimes nationally, but all pretty standard Excel functionality.

Recently I was looking at a school's tracking spreadsheet for progress in internal testing. It looked like this:
Basic functionality of taking the scores for the 10 individual tests and calculating an average using the Excel function =Average(data range).
The school wanted to use this data to suggest what set the students should be placed in. As a result, they wanted to change the way that they calculated the averages. They wanted to:
  1. Remove the highest score from the average calculation to avoid biasing the average with a particularity high (but one off) test score
  2. Only average the top 3 scores (once the highest had been removed)
Currently this was a manual process involving sorting and ranking the data for each student and then calculating the average of the highest 3 remaining scores. Quite a lengthy and manual process, prone to numerous errors. The school asked if I could automate this process for them.
The Solution (colours added for emphasis only)
It turns out that Excel can achieve this by stacking some of the built in functions.
  • Large(range,nth) - returns the nth term from a range. So large(A1:J1,1) will return the 1st largest (ie biggest) term from the range A1:J1. 2, 3 etc would return the second and third largest.
  • We want the 2,3,4 (for the second, third and fourth terms) - we want to omit the first term (1) - and we want the data for the 2nd, 3rd and 4th. To do this, we use an array - we pass {2,3,4} into the Large formula - Large(A1-J1, {2,3,4}) - returns the data we want as an array (2nd, 3rd, 4th) - in the case of Student A, this would return (44,42,35) - with the 48 being overlooked.
  • We then want to average this data array with the Average(data) formula - Average(Large(A1-J1, {2,3,4}))
  • We then want to round that to 0 decimal places with Round(data,0) giving ROUND((AVERAGE(LARGE(A1:J1,{2,3,4}))),0)

  • We could swap Large() for Small() which would then average the bottom n terms in the list

comments powered by Disqus