Average the Largest 3 Values from the Last 5 Values
The following formulas return an average of the largest 3 values from the last 5 values in a range of cells. The first example is based on data that does not contain blank cells, whereas the second example is based on data that does contain blank 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