Average of three highest values in Excel
A recent questions we received asked how we can get the average of the three highest values in an Excel spreadsheet. So for example if you have the following for a student
Student A test results
- Test 1 = 15
- Test 2 = 46
- Test 3 = 60
- Test 4 = 74
You want the answer of 60 being the average of 46, 60, and 74 (the 15 is dropped as it is not in the top 3). For this we can use a combination of the AVERAGEIF, LARGE and & functions.
You can either do this all in a single cell or separately. We will show it as if we are using separate cells
Assuming that the table above is in A1 to B4.
In a separate cell (say B7) work out the 3rd largest value ( = LARGE(B1:B4, 3).
Create an AVERAGEIF (say in cell B8) that refers to the test scores. As the criteria you need to create a concatenate of the ‘>=’ sign and the 3rd largest number. You do this by including in the AVERAGEIF “>=”&the cell where the LARGE is used.
The formula will look something like this
These are the types of things you will learn on our courses.