Let say you have a line-up of 20 products in your company and you have data for monthly sales of all the 20 products.
From that data, you want to calculate the average of top 5 products. In short, you want to get the average of top 5 values from that data.
Look at the below example.
Formula: Average TOP 5 Values
To average the top 5 scores from the list, you can use a formula based on the combination of LARGE and AVERAGE.
And, the formula will be:
How this formula works
To understand this formula, you need to split it into two parts.
In the first part, we have used the LARGE function.
The LARGE function can return the nth largest value from data. If you specify 2 as the nth value it will return the second highest value from the data.
Here you need top 5 values, not just one so that you will average them. For this, you need to enter below array as nth value.
When you enter this into the function, it returns an array of top 5 values, just like this below.
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.