Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
3 cells are named - how to refere to them in one reference field in a chart | Charts and Charting in Excel | |||
GET.CELL | Excel Worksheet Functions | |||
Using the results from two seperate cells to create cell reference | Excel Worksheet Functions |