Average of Last 10
Biff wrote...
=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6),
--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6)
If there are <10 values, returns #N/A.
.....MATCH(MIN(COUNT(A6:W6),10).......
Good point, if averages of fewer than 10 values should be permitted.
Still doesn't account for COUNT = 0
So, maybe:
=IF(COUNT(),AVERAGE(INDEX........................ ..)):W6),"")
Depends. AVERAGE of a range of blank cells returns #DIV/0!, which is
the correct result. After your change, using MIN, the array formula
would also return #DIV/0! if count were 0. Sometimes error results are
the correct and expected result.
|