Average on the 6 latest figures of a list where figures keep being added

**patounet527** · 03-26-2009, 11:43 PM

Well, thanks for your reply, in principle, it works. My problem is the following: if at all possible, I think I'd limit the range of the cells storing data to A1 - A99, continue to have only the 6 latest figures taken into consideration for the average calculation, and then on cell A100, I'd like to have this function running.

But I notice if I merely try to adjust your formula so as to refer to only part of a column, the result is crooked. Any suggestion?

**DonkeyOte** · 03-27-2009, 03:54 AM

I'm not quite sure how you went about it... if you change every reference of A:A to A1:A99 it should work, eg:

A100:
=AVERAGE(INDEX(A1:A99,MATCH(9.99999999999999E+307,A1:A99)-5):INDEX(A1:A99,MATCH(9.99999999999999E+307,A1:A99)))

However it would not work if you had < 6 values in your range... so you could introduce a MIN clause to average at most 6 values or at least x values where x is determined by the number of values in the range if less than 6, eg:

=AVERAGE(INDEX(A1:A99,MATCH(9.99999999999999E+307,A1:A99)-MIN(5,COUNT(A1:A99)-1)):INDEX(A1:A99,MATCH(9.99999999999999E+307,A1:A99)))

And perhaps add one further test to ensure you have at least one number in the range

=IF(COUNT(A1:A99),AVERAGE(INDEX(A1:A99,MATCH(9.99999999999999E+307,A1:A99)-MIN(5,COUNT(A1:A99)-1)):INDEX(A1:A99,MATCH(9.99999999999999E+307,A1:A99))),"")