# 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