Thread: Help with formula to get an average of 1st, 3rd, and 5th highest numbers

Hi Guys,

I've just signed up to this forum in the hope that someone can help me with the problem below.

Basically, Im a university student and im trying to create a simple workbook to give me my final university degree grade.

I have a list of 5 modules that im studying in collum B, with 5 grades next to them in collum C. What i would like to know, is how to create a formula that takes an average of the highest grade (1st), 3rd highest, and the lowest (5th). This grades can change at any time so i would like to be able to change them without any for the formula messing up, and for the calculation still to work out the average of these 3 numbers.

Obviously the easiest way to do this would be to sort the grades from highest to lowest, and then calculate the average of the 1st, 3rd, and 5th cell, but is there a way of doing this without hitting the sort button every time?

Many Thanks in advance!

Ryan

Try:

=AVERAGE(MIN(B1:B5),MEDIAN(B1:B5),MAX(B1:B5))

or

=AVERAGE(LARGE(B1:B5,{1,3,5}))