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

 

[njimack]

Enter the following into B3 and copy across as far as P3:
=AVERAGE(OFFSET(B2,,-MIN(4,COLUMN()-2),,MIN(5,COLUMN()-1)))

 

[imperium1980]

HI, thanks for trying but this doesn't seem to make any difference?

Regards, Neil

 

[njimack]

Could you be more specific?

Can you post the values you have in B2:P2, and the result of my formula in B3:P3?

 

[imperium1980]

B2:P2 as follows...20 40 30 12 18 16 31 29 30 25 31 40 20 15 10

I've pasted your formula into B3 and dragged across, this gives me B3£ as follows...20 30 30 26 24 23 21 21 25 26 29 31 29 26 23

A1 still shows #DIV/0!

Thanks for helping me out on this.

 

[njimack]

OK, so my formula is working as expected in that it calculates the average of the last 5 cells. Where there are less than 5 previous cells, it reduces the range.

What do you want to return in A1?

 

[imperium1980]

It should report the average from row 3 where the last value was entered. e.g. if N2 to P2 were blank, it should return 31 from M3.

 

[njimack]

=average(offset(b2,,,,counta(b22)))

EDIT: Ignore that - working on revised formula

 

[njimack]

This should give you the average of the last 5 cells in the range B2:P2:

=AVERAGE(OFFSET(B2,,MAX(COUNTA(B2:P2)-1,COUNTA(B2:P2)-5),,-MIN(COUNTA(B2:P2),5)))

 

[imperium1980]

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

Neil