Returning a moving average across colums

imperium1980

New Member
Joined
Feb 10, 2009
Messages
27
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
Enter the following into B3 and copy across as far as P3:
=AVERAGE(OFFSET(B2,,-MIN(4,COLUMN()-2),,MIN(5,COLUMN()-1)))
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
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

New Member
Joined
Feb 10, 2009
Messages
27
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:p£ 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

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
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

New Member
Joined
Feb 10, 2009
Messages
27
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
=average(offset(b2,,,,counta(b2:p2)))

EDIT: Ignore that - working on revised formula
 
Last edited:

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,078
Messages
5,466,484
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top