Returning a moving average across colums

[imperium1980]

Hi all,
There are any number of posts on this topic but all are based on data in adjacent columns. I have to get a cell to return the average of the last 5 values entered in a row but cannot get it to work. Probably because I can't fully understand what the row and column settings mean in the offset formula.

I have the folowing formula set up in A1.
=AVERAGE(OFFSET(B2,COUNT(B2:P2)-1,0,1,5))

In A2 I have "Produced" then some small integers from B2 to P2

In A3 I have "Average" then in G3 Formula =AVERAGE(C2:G2), H3 is =AVERAGE(D2:H2) etc.

Can anyone enlighten me as to what is wrong with my formula? Any help greatly appreciated.

Regards,
Neil

 

[ShayB1028]

This is exactly the formula I need, but I can't get it to work. I think it's because I don't exactly understand the pieces of it. I have a spreadsheet and I need to produce in cell G2 the average of the last 3 populated cells for cells H2:S2 (these cells contain monthly sales for specific items). I will need to duplicate this formula in cell F2 for the last 6 cells populated. The Formula I used is as follows...
=AVERAGE(OFFSET(H2,,MAX(COUNTA(H2:S2)-1,COUNTA(B2:P2)-3),,-MIN(COUNTA(H2:S2),3)))

This didn't work. Can you please let me know where I made the mistake? Thank you very much for your help!!

Works perfectly! Thanks for all the help on this, really appreciate yout time.

Neil