average of last 5 in a table

Hi

I have a table that i update every week with a new rate of pay, from which i have an automatic calculation of the overall average. How do i have a calculation of the last 5 weeks rate of pay?

Eg for week 20, if data is in a1:a20, i want the average of a16:a20
for week 21, if data is in a1:a21, i want the average of a17:a21 etc etc, but automatic calculation

thanks!

Try something like

=AVERAGE(INDEX(A:A,COUNT(A:A)-4):INDEX(A:A,COUNT(A:A)))

Hi Cutter

that has returned an error, sorry! I've actually not explained myself correctly aswell sorry. ive attached a bit of the workbook to help explain.

the formula will need to go in cells AO3, AO4, AO5 and so on. as you will see, there are lots of blank cells but these will be filled in weekly and would like and average of the last 5 entered figures. hope ive explained this better this time!

thanks

Hi, Petelomax,
I've tried Cutter's formula and got it to work fine, you just need to replace every instance of A:A with $D3:$AN3 on row 3. (This can then be dragged down as far as you need it to go:

=AVERAGE(INDEX($D3:$AN3,COUNT($D3:$AN3)-4):INDEX($D3:$AN3,COUNT($D3:$AN3)))

I wasn't just duplicating Jeff's reply - honest! It just took me longer to write it

thanks everyone for your replies, this works a treat now

Hi, just an extension on this, there has to be at least 4 figures in the last 4 columns for this to work, but sometimes there are products with just one rate of pay and it throws up an error

ive attached a document to help explain this. thanks.

You need to do the count on a row that is guaranteed to always have something to count - from start to end.
So, row 2 would be such a row. But it's text, not numeric. So, instead of COUNT() use COUNTA().

Like this: =AVERAGE(INDEX(D8:AN8,COUNTA(D$2:AN$2)-4):INDEX(D8:AN8,COUNTA(D$2:AN$2))) for cell AO8 and copied throughout

Hi Cutter

Thanks very much for this, works a treat! thanks!

You're welcome. Thanks for the 'star tap'.