always calculate last 5 rows even after 1 inserted
 

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.

always calculate last 5 rows even after 1 inserted
 

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


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

Hi,

In 2003 and earlier you could use this

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

In 2007 this fails.

--
Thanks,
Shane Devenshire


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

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.

--
Thanks,
Shane Devenshire


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

It's not an elegant formula:
=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A2 0<""),{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



"ShaneDevenshire" wrote:

Hi,

In 2003 and earlier you could use this

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

In 2007 this fails.

--
Thanks,
Shane Devenshire


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

=SUMPRODUCT(SUM(OFFSET(A1,LARGE(ROW(A1:A20)*(A1:A 20<""),{1,2,3,4,5})-1,)))/5
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.

--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
Hi,

In 2003 and earlier you could use this

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

In 2007 this fails.

--
Thanks,
Shane Devenshire


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

Try......

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

Regards
Bosco

"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

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

"Teethless mama" wrote:

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


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

That worked like a charm, thank you.

"ShaneDevenshire" wrote:

Hi,

In 2003 and earlier you could use this

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

In 2007 this fails.

--
Thanks,
Shane Devenshire


"Picman" wrote:

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.

always calculate last 5 rows even after 1 inserted
 

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

"bosco_yip" wrote:

Try......

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

Regards
Bosco

"Picman" wrote:

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.