Suppose that you have a list of numbers and users want the average of top (Max) numbers like first, fourth, sixth or any highest or lowest values from the array.
Query: We have Month and Employee Name with sales. Our task is to find the average of third, fourth, fifth and sixth largest sales in the given range.
Download Your Example Excel File
Formula to implement:
Average: Get an average of numbers positioned in one or multiple ranges.
Large: The Excel LARGE function returns the highest numeric value in the given range based on their position (k) i.e. the top value in the array.
Row: The Excel ROW function returns the row number of a reference cell.
Formula Explanation (step by step)
Let’s break the formula into multiple small steps to understand its application.
Result: 3,4,5 and 6.
It will give row numbers 3, 4, 5 and 6.
Result: 383,381,355, 350
As we already get the row number third, fourth, fifth, and sixth, now it returns highest number in the given range (C3:F8).
Syntax: AVERAGE(number1, [number2], …)
It will give the average number of selected top numbers based on the rank (k).