How do I reference the last 3 cells in a row as cells are added

[Guest]

I am trying calculate a rolling three month average as each months numbers
are added. For example: If I have values for all the months up to July, I
want to average May, June and July. When August is added I want it to
average June, July and August. I have been doing this by changing the range
in the formula each month, but would like to make it more efficient and user
friendly.
Thanks!

 

[Don Guillett]

try this idea

Sub last3cells()
x = Cells(Rows.Count, "b").End(xlUp).row
Range("b" & x - 2).Resize(3, 1).Select
End Sub

 

[Guest]

How many values per month are there? Is this a total figure or a column of
data?
Here is a way to do this if the numbers you want to average are stored in
Row 2 and each new month is added in its own Column with no blank Columns
between months:
Define a Named Range as a Dynamic Range.
Insert | Name | Define

In the Names line, type:
LastThreeMonths

In the refers to line type:
=OFFSET(Sheet3!$A$2,0,COUNTA(Sheet3!$2:$2)-3,1,3)

For your Formula, type =AVERAGE(LastThreeMonths)

As you add new months this Range should update automatically.
Remember, the above example defines a Named Range based on 1 Row of Data. In
this case, Row 2. It also assumes there is no other information in Row to
other than the Numbers to be Averaged.

For more on Dynamic Ranges:
http://www.cpearson.com/excel/named.htm

tj

 

[Guest]

Typo correction.

This:
It also assumes there is no other information in Row to
other than the Numbers to be Averaged.

Should have been:
It also assumes there is no other information in Row 2
other than the Numbers to be Averaged.

 

[Guest]

Thank you! there is only one value per month and they are in a row without
any additional data so your solution should work perfectly.

 

[Don Guillett]

Sub Average3cellsOffset()
x = Cells(Rows.Count, "b").End(xlUp).row
y = Application.Average(Range("b" & x - 2).Resize(3, 1).Offset(0, 1))
MsgBox y
End Sub

 

[RagDyeR]

Say your monthly totals are in A15 to L15.

Try this *array* formula in M15:

=AVERAGE(L15:INDEX(A15:L15,LARGE(COLUMN(A15:L15)*(A15:L15>0),3)))

Entered with <Ctrl> <Shift> <Enter>,
Instead of just <Enter>.
This will *automatically* enclose the formula in curly brackets, which
*cannot* be done manually!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Thank you! there is only one value per month and they are in a row without
any additional data so your solution should work perfectly.