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

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!

try this idea

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

--
Don Guillett
SalesAid Software

"Still Learning" <Still wrote in message
...
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!

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


"Still Learning" wrote:

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!

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.

"tjtjjtjt" wrote:

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


"Still Learning" wrote:

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!

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.

"tjtjjtjt" wrote:

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


"Still Learning" wrote:

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!

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

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this idea

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

--
Don Guillett
SalesAid Software

"Still Learning" <Still wrote in
message
...
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!

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:L150),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!
==============================================



"Still Learning" wrote in message
...
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.

"tjtjjtjt" wrote:

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


"Still Learning" wrote:

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!