Average the Largest 3 Values from the Last 5 Values
The formulas in the following examples return an average of the largest 3 values from the last 5 values in a range of cells.
Without Blank Cells in the Data
The following formula returns the average of the largest 3 values from the last 5 values in A2:A10, where the data does not contain blank cells...
=AVERAGE(LARGE(INDEX(A2:A10,MATCH(9.99999999999999E+307,A2:A10)-5+1):A10,{1,2,3}))
Based on the sample data, the formula returns 70.
Sample Workbook: Download
With Blank Cells in the Data
The following formula returns the average of the largest 3 values from the last 5 values in A2:A10, where the data contains blank cells...
=AVERAGE(LARGE(INDEX(A2:A10,LARGE(IF(A2:A10<>"",ROW(A2:A10)-ROW(A2)+1),5)):A10,{1,2,3}))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 75.
Sample Workook: Download