How to Calculate Average of Top 5 Values in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Making an average of your dataset is a very usual work of your day-to-day life. In Excel, we can create an average of the dataset using the AVERAGE function only. But it becomes a tough job when you have to calculate the average of the top 5 numbers or an average of numbers having specific criteria. What are you thinking? Is it possible or not? Luckily, Excel provides us with diverse functions to calculate the average of the top 5 values. You don’t need to get worried. Here in this article, we have tried to cover the methods to an average of the top 5 values of a dataset in Excel. Read this article to get a basic visualization.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


5 Simple Methods to Calculate Average of Top 5 Values in Excel

Excel offers us enormous functions. Among them, there are some functions that can be used to an average of the top 5 values in Excel. In addition, we use some other functions to find out the top 5 values and then do the average. Here we make a dataset of Student’s Marks. We want to know the average marks of the top 5 marks.

Dataset

Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.


1. Using AVERAGE and LARGE Functions

Basically, the LARGE function finds out the n-th largest value from the array or range of cells selected based on the serial number. You have to put the number for what the function will find out the largest number. We want to know the top 5 values so we will put the n-th number as 5. The AVERAGE function will then make the average of that values. If you are confused about this method, then follow the steps below to get a proper idea.

📌 Steps:

  • In the very beginning, go to cell C14 where we want to show the average.
  • Write down the below formula.
=AVERAGE(LARGE(C5:C12,{1,2,3,4,5}))

Here,

C5:C12= The range of the Marks.

Formula Breakdown:

LARGE(C5:C12,{1,2,3,4,5})→ the function finds out the {1,2,3,4,5} (five) largest value among the entire range of C5:C12. Then it returns the top 5 largest values as an output.

  • Output→96,92,90,89,88

AVERAGE(LARGE(C5:C12,{1,2,3,4,5}))→ This function will calculate the average of the returns value of the LARGE function.

  • Output→91

Using AVERAGE and LARGE Functions to average top 5 values in Excel

  • Finally, you will get the average of the top 5 marks.

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)


2. Employing INDEX and MATCH Functions

The MATCH function looks out for a specific value among a range of datasets and then returns the position of that value in the cells. Then the INDEX function will return the reference value mentioned by the MATCH function. Here, we will find out the top 5 values with these functions. Follow the steps to do so.

📌 Steps:

  • First of all, select cell E5 and write the formula.
=INDEX($B$5:$B$12, MATCH(LARGE($C$5:$C$12,ROWS($E$5:$E6)),$C$5:$C$12,0))

Here,

$B$5:$B$12= refers to the column of Students.

$C$5:$C$12= Cell of Marks.

$E$5:$E6= the cell where we will show the top 5 values.

Formula Breakdown:

  • ROWS($E$5:$E6)→ ROWS function inputs the serial number for the LARGE function. Here it will take the output of cell E6.
  • LARGE($C$5:$C$12, ROWS($E$5:$E6)→The LARGE function finds out the largest value from the array or range of cells selected based on the serial number. It takes the result of the ROWS function and looks for the largest value among cell $C$5:$C$12.
  • MATCH(LARGE($C$5:$C$12, ROWS($E$5:$E6)),$C$5:$C$12,0)→ MATCH function looks for the obtained largest value in the array of values & returns with the row number of that value.
  • INDEX($B$5:$B$12, MATCH(LARGE($C$5:$C$12, ROWS($E$5:$E6)),$C$5:$C$12,0))→ INDEX function finally pulls out the name from the column of Students based on that row number found by the MATCH function.

Employing INDEX and MATCH Functions to average top 5 values in Excel

  • Now, go to cell F5 and write down the formula.
=LARGE($C$5:$C$12,ROWS($F$5:$F5))

The syntax LARGE($C$5:$C$12, ROWS($F$5:$F5)) implies that the LARGE function will find out the largest value by taking the serial number produced by the ROWS function.

Using LARGE function to average top 5 values in Excel

  • Later, go to cell F11 and write the formula
=AVERAGE(F5:F9)

This formula will make an average of the range F5:F9.

  • Finally, your average of the top 5 values will be displayed.

Employing INDEX and MATCH Functions

Read More: Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)


3. Utilizing XLOOKUP and VLOOKUP Functions

The XLOOKUP function will search from top to bottom of a lookup_array and then return the value corresponding to the first match found by it. Also, we use the LARGE and ROWS functions to get the top 5 values. Then we have done the average of the top 5 values. We demonstrate to you the procedures to use the XLOOKUP function to get the average.

📌 Steps:

  • Initially, go to cell E5 and write up the formula
=XLOOKUP(LARGE($C$5:$C$12,ROWS($E$5:$E5)),$C$5:$C$12,$B$5:$B$12)

Here,

In the 1st argument of the XLOOKUP function, the largest value has been inputted. 2nd argument is the Range of Cells C5:C12 where the selected largest value will be looked for. And the 3rd argument is another range of cells B5:B12 from where the particular data or name will be extracted based on the row number found by the 1st two arguments.

Utilizing XLOOKUP and VLOOKUP Functions to average top 5 values in Excel

  • Press ENTER and drag down the Fill Handle tool for other cells.

Fill handle

  • After that, in cell F5 write down the formula.
=IFERROR(VLOOKUP(E5,B5:C12,2,FALSE),1)

Formula Breakdown:

VLOOKUP(E5, B5:C12,2, FALSE),1)→VLOOKUP will look for the output for the entered value in E5 from the range of B5:C12 and returns the marks for the corresponding value of E5

IFERROR(VLOOKUP(E5, B5:C12,2, FALSE),1)→This function will find out the error in the argument. If any error occurs, it will return the value.

Using VLOOKUP to average top 5 values in Excel

  • Afterward, you will get your top 5 values just like the image below.

  • Lastly, select cell F11 and write up the formula of average.
=AVERAGE(F5:F9)

Average top 5 values in Excel

  • Eventually, your average of the top 5 values will be created.

Read More: How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)


Similar Readings


4. Applying ROW and INDIRECT Functions

Mainly, the ROW function is a lookup function that searches the range of a dataset and returns the rows that are within the range and the INDIRECT function will return a specific reference cell for the given criteria. In this method, we will set a Starting Number and an Ending Number then we will apply the formula to get the average of the top 5 values.

📌 Steps:

  • Primarily, go to cell F7 and write the following formula,
=AVERAGE(LARGE(IF(C5:C12>0,C5:C12),ROW(INDIRECT(E5&”:”&F5))))

Formula Breakdown:

INDIRECT(E5&”:”&F5)→ This function will lock the reference cell E5 and F5.

ROW(INDIRECT(E5&”:”&F5))→ This function will take the Starting Number value 1 and Ending Number value 5.

LARGE(IF(C5:C12>0, C5:C12), ROW(INDIRECT(E5&”:”&F5)))→ The IF function will check if there is any blank cell, if not then the function returns the cell C5:C12 and as we said before, the LARGE function will find the 5 largest value.

AVERAGE(LARGE(IF(C5:C12>0,C5:C12),ROW(INDIRECT(E5&”:”&F5))))→ This function will calculate the average of the return result.

Applying ROW and INDIRECT Functions to average top 5 values in Excel

  • Finally, you will get your result.

Read More: How to Find Average of Specific Cells in Excel (3 Handy Ways)


5. Using FILTER Function

We will filter the dataset with specific criteria to display the top 5 values among our datasets. Then we will make an average of them. For doing this, we use the FILTER function to sort out the value that matches our criteria. Follow the simple steps to do that.

📌 Steps:

  • Firstly, move to cell F5 and write down the formula.
=FILTER(C5:C12,C5:C12>=LARGE(C5:C12,5))

Formula Breakdown:

LARGE(C5:C12,5)→ The function will find out the top 5 values as we entered argument 5 among C5:C12.

FILTER(C5:C12, C5:C12>=LARGE(C5:C12,5))→ The FILTER function will filter the range C5:C12 and shows the largest 5 values.

Using Filter Function to average top 5 values in Excel

  • Now, select cell F11 and write the average formula.
=AVERAGE(F5#)

It will calculate the average of the entered argument F5#.

  • Finally, you will get the result.

Read More: How to Average Filtered Data in Excel (2 Easy Methods)


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy methods to an average of the top 5 values in Excel Please let us know in the comments section if you have any questions or suggestions. For your better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.

Happy Excelling! 😊


Related Articles

Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo