Need Help with Array Formula

[sjd319]

I am looking for an formula that will give me the average of the last 10 entries in a column.
I have tried the following formulas, but they are not giving me the correct average:
=AVERAGE(OFFSET(N$1,MAX(COUNT(N:N)-10,0),,10))
=INDEX(Sheet2!$A$1:$E$5,MATCH($A1,Sheet2!$A$1:$A$5,0),COLUMN())

Any help would be appreciated.

[NBVC]

How is this one not working?

=AVERAGE(OFFSET(N$1,MAX(COUNT(N:N)-10,0),,10))

If you have numbers in consecutive cells from N1 down, then it works.

if there are blanks at the top of the list, this is an alternative:

=AVERAGE(OFFSET(N$1,MATCH(9.9999E+307,N:N)-10,,10))

[sjd319]

You are getting close i think. there are empty cells between entries. is there a formula that will only average those cells, the last 10 entries, that have values in the cells?

[NBVC]

Perhaps then an array formula:

=AVERAGE(IF(N1:N100<>0,IF(ROW(N1:N100)>=LARGE(IF(N1:N100,ROW(N1:N100)),10),N1:N100)))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

Note: It is not recommended to use whole columns with array formulas due to inefficiency... but you can use a dynamic named range...

Define a new Named range (through formula tab) as "List" with formula: =Sheet2!$N$1:INDEX(Sheet2!$N:$N,MATCH(9.99E+307,Sheet2!$N:$N))

then change average formula to:

=AVERAGE(IF(List<>0,IF(ROW(List)>=LARGE(IF(List,ROW(List)),10),List)))


confirmed with CTRL+SHIFT+ENTER not just ENTER.

[sjd319]

you got it NBVC. so many thanks. i have been screwing around with this formula for years. made my day. thanks again.

[darknation144]

Please mark the thread as solved. Check the FAQ's to see how.