|
|
| Average
the Last 3 Values |
Without Blank Cells in the Data
The
following formulas return the average for the last 3
values in B2:B10, where the data does not contain blank
cells...
=AVERAGE(INDEX(B2:B10,MATCH(9.99999999999999E+307,B2:B10)-3+1):B10)
or
=AVERAGE(OFFSET(B2:B10,MATCH(9.99999999999999E+307,B2:B10)-3,0,3))
Based on the sample data, the formulas return 80. Sample
Workbook
With
Blank Cells in the Data
The
following formula returns the average for the last 3
values in B2:B10, where the data contains blank
cells...
=AVERAGE(INDEX(B2:B10,LARGE(IF(B2:B10<>"",ROW(B2:B10)-ROW(B2)+1),3)):B10)
| 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 80. Sample
Workbook
|
|
|
