I've seen similar posts on this topic and found a few that work fine when using the array formula to do this calculation down a column but all my efforts to do it across a row will not provide exact results.
I'm attempting to average each players last 10 weeks scores. It doesn't matter if they play every week, I just need the scores of their last 10 outings averaged.
In this example, Column (B) uses the average function and manually selected columns with data.
I have two other columns where I've attempted using:
OFFSET Column (C)
=AVERAGE(OFFSET(F4,0,0,1,SMALL(IF(ISNUMBER(F4:BC4),ROW(F4:BC4)),10)))
and INDEX Column (D)
=AVERAGE(F4:INDEX(F4:BC4,LARGE(IF(F4:BC4<"",COLUMN( F4:BC4)),MIN(COUNT(F4:BC4),10))))
The results are usually pretty close to the actual average, but that isn't going to cut it.
Any help would be greatly appreciated
Hopefully the attachment comes across.
Bookmarks