Fahim Shahriyar Dipto
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.
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.
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.
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.
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
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
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.
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.
- 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,
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.
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.
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
