**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.

**Table of Contents**hide

## 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.

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

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

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

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

**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.

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

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

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

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

**How to Calculate Sum & Average with Excel Formula****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****How to Calculate Average from Different Sheets in Excel****Average Every Nth Row in Excel (3 Ways)****How to Average a Column in Excel (7 Easy Methods)**

### 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.

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

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

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

**How to Calculate Average of Averages in Excel (with Easy Steps)****Calculate Average Only for Cells with Values in Excel****How to Calculate Average of Multiple Columns in Excel (6 Methods)****Exclude a Cell in Excel AVERAGE Formula (4 Methods)****How to Calculate Average in Excel Excluding 0 (2 Methods)****Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)****How to Ignore #N/A Error When Getting Average in Excel**