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

**patounet527** · 03-26-2009, 12:13 PM

Hi!
I need to set up a worksheet on which I can automatically retrieve the average value of the 6 latest figures showing up. The challenge is that the list keep the same starting values, and monthly new values are added on, so the formula must include a reference not to cells but the the selection of the farthest down on the list. Any kind of suggestion would be appreciated!
Here an example:

32
435
657
677
34
56
456
56
56
56
Formula needed: average of cells containing 34, 56, 456, 56, 56, 56

Next month, however, that would look as follows:

32
435
657
677
34
56
456
56
56
56
28
Formula needed: average of cells containing 56, 456, 56, 56, 56, 28

Thanks for your help!

**DonkeyOte** · 03-26-2009, 12:15 PM

Assuming no blanks interspersed within the numbers then:

=AVERAGE(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)-5):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

or

=AVERAGE(OFFSET(A1,MATCH(9.99999999999999E+307,A:A)-1,,-6,1))

INDEX IMO would be preferable to OFFSET given Volatility

**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))),"")