✨ OfficeTricks

SUM and Average of Top N Values in Excel

📅 Published: 2020-10-30 📁 Category: Excel, Excel Formula Tricks, Excel VBA Macro
In today's world, the data sets we have is huge and we can have so many criteria to be followed on the data For Example:
  • Sum
  • Average etc.
Let us say we have N products in our superstore and we have to find the total number of sales made in that particular month. Let N be 20 for the sake of this example From the given data, we want to calculate the total of the top 15  most selling products in our superstore.

Formula: Sum of TOP 15 Values

The sum of the top 15 sales value from the list,  you can use the formula based on the combination of LARGE and SUM The formula is:

=SUM(LARGE(B1:B20,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))

How this formula works It is simple yet powerful for a large number of data sets. In the first part of the formula, we have used the LARGE function.

LARGE Function:

The LARGE function can return the nth largest value from data. If we specify 5 as the nth value it will return the fifth highest value from the data. The LARGE function fetches the 15 highest data from the sheet and then applies some calculation on the data. In this example, we are applying the SUM function When you enter this into the function, it returns an array of top 15 values. In the end, the sum function returns the SUM of those values.

Formula: Average of TOP 15 Values

The average of the top 15 sales value from the list,  you can use the formula based on the combination of LARGE and AVERAGE. This is quite the same as the SUM function. The formula is:

=Average(LARGE(B1:B20,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))

How this formula works It is simple yet powerful for a large number of data sets. In the first part of the formula, we have used the LARGE function. When you enter this into the function, it returns an array of top 15 values. In the end, the average function returns the AVERAGE of those values.