always calculate last 5 rows even after 1 inserted

[Picman]

i want to always calculate the average of the bottom 5 rows of a longer list
even after a new row is inserted and the list is resorted.

 

[Teethless mama]

=AVERAGE(INDEX(A:A,MATCH(10^10,A:A)-4):INDEX(A:A,MATCH(10^10,A:A)))

 

[ShaneDevenshire]

Hi,

In 2003 and earlier you could use this

=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A20<>""),{1,2,3,4,5})-1,)))/5

In 2007 this fails.

 

[ShaneDevenshire]

Hi,

Back again with a 2007 solution:

=AVERAGE(INDIRECT("A"&MAX((A1:A21<>"")*ROW(A1:A21))-4&":A"&MAX((A1:A21<>"")*ROW(A1:A21))))

This formula must be array entered - press Shift+Ctrl+Enter to enter it.

 

[Teethless mama]

It's not an elegant formula:

=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A20<>""),{1,2,3,4,5})-1,)))/5

Here is an elegant solution, but also works for XL-2007

=SUM(OFFSET(INDIRECT("A"&ROWS(A1:A20)),,,-5))/5

 

[T. Valko]

In 2007 this fails.

Very interesting.

I wonder why? It should.

In 2007 the SUM function isn't accepting the results of OFFSET but it does
in Excel 2002 (my other version).

This works in 2007:

=SUMPRODUCT(N(OFFSET(A1,LARGE(ROW(A1:A21)*(A1:A21<>""),{1,2,3,4,5})-1,)))/5

I'm going to post this in the private ng.

 

[bosco_yip]

Try......

=SUM(OFFSET(INDIRECT("A"&ROWS(A1:A20)),,,-5))/5

Regards
Bosco

 

[Picman]

This didn't work. it didn't even return the correct result before adding a row.

 

[Picman]

That worked like a charm, thank you.

Hi,

In 2003 and earlier you could use this

=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A20<>""),{1,2,3,4,5})-1,)))/5

In 2007 this fails.

 

[Picman]

That worked great as well, and a lot cleaner, thank you.