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.