|
|
| Average
the Largest 3 Values from the Last 5 Values |
Without Blank Cells in the Data
The
following formulas return the average for the largest 3 values from the
last 5 values in B2:B10, where the data does not contain blank
cells...
=AVERAGE(LARGE(INDEX(B2:B10,MATCH(9.99999999999999E+307,B2:B10)-5+1):B10,{1,2,3}))
or
=AVERAGE(LARGE(OFFSET(B2:B10,MATCH(9.99999999999999E+307,B2:B10)-5,0,5),{1,2,3}))
Based on the sample data, the formulas return 70. Sample
Workbook
With
Blank Cells in the Data
The
following formula returns the average for the largest 3 values from the
last 5 values in B2:B10, where the data contains blank
cells...
=AVERAGE(LARGE(INDEX(B2:B10,LARGE(IF(B2:B10<>"",ROW(B2:B10)-ROW(B2)+1),5)):B10,{1,2,3}))
| Note
that this is an array formula, which needs
to be confirmed with
CONTROL+SHIFT+ENTER for the PC or COMMAND+RETURN for the Mac. |
Based on the sample data, the formula returns 75. Sample
Workbook
|
|
|
