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.