If you want to find the average of the largest 3 values from last 5 values,you can use the combination of “AVERAGE”, “INDEX”, “MATCH” & “LARGE” function to retrieve the output.
AVERAGE function can be used to find the average value or arithmetic mean of values in a selected range of cells.
number1, number2,……number n are numeric values.They can be numbers or names, arrays, of references that contain numbers.
INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
array:Array is a range of cells or table.
row_num:The row number in the array from which to return the value.
column_num:It is optional. It is the column number in the array used to return the value.
MATCH function searches for a specified item in a selected range of cells, and then returns the relative position of that item in the range.
lookup_value: The value you want to look for
lookup_array: The table of data contains information from which you want to return the output.
match_type: 1,0 and -1 are three options.
1(Default): It will find the largest value in the range. List must be sorted in ascending order.
0: It will find an exact match
-1: It will find the smallest value in the range. List must be sorted in descending order.
LARGE: Returns the kth largest value in a data set. For example, the second largest number from a list of 10 items.
array: Itis an array or range of cells in a list of data for which you want to find the k-th largest value.
k: is the kth position from largest value to return in the array or range of cells.
Let us take an example:
We have some numbers in column A.We want a formula that will give the average of largest 3 values from the last 5 values.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.