| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling data
The sheet calculates 6mo totals and averages. Each month, a new column is
inserted and added to the totals & average, while the one from six months ago is dropped. (column is retained for other reasons, just no longer included in these calculations). Is there a way to do this without having to update the column references for the calculations every month? Thanks in advance, Wal50 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Try something like this:
With values in B2:i2 This formula sums the 6 cells immediately before J2 J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) If you insert a column before Col_J, the formula, of course, moves into K2 and automatically adjusts so it sums the 6 cells immediately before K2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "wal50" wrote: The sheet calculates 6mo totals and averages. Each month, a new column is inserted and added to the totals & average, while the one from six months ago is dropped. (column is retained for other reasons, just no longer included in these calculations). Is there a way to do this without having to update the column references for the calculations every month? Thanks in advance, Wal50 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Thanks Ron. That looks like it does the trick.
WAL50 "Ron Coderre" wrote: Try something like this: With values in B2:i2 This formula sums the 6 cells immediately before J2 J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) If you insert a column before Col_J, the formula, of course, moves into K2 and automatically adjusts so it sums the 6 cells immediately before K2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "wal50" wrote: The sheet calculates 6mo totals and averages. Each month, a new column is inserted and added to the totals & average, while the one from six months ago is dropped. (column is retained for other reasons, just no longer included in these calculations). Is there a way to do this without having to update the column references for the calculations every month? Thanks in advance, Wal50 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Glad to help....and thanks for the feedback.
*********** Regards, Ron XL2002, WinXP "wal50" wrote: Thanks Ron. That looks like it does the trick. WAL50 "Ron Coderre" wrote: Try something like this: With values in B2:i2 This formula sums the 6 cells immediately before J2 J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) If you insert a column before Col_J, the formula, of course, moves into K2 and automatically adjusts so it sums the 6 cells immediately before K2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "wal50" wrote: The sheet calculates 6mo totals and averages. Each month, a new column is inserted and added to the totals & average, while the one from six months ago is dropped. (column is retained for other reasons, just no longer included in these calculations). Is there a way to do this without having to update the column references for the calculations every month? Thanks in advance, Wal50 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Ron,
J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) resolves one question - thanks again. And experimenting has shown me this can also be used for columns after J by changing to +6 (or +12) and the -1 to +1. What does the "other" 1 control? I also found =SUM(IF(MOD(COLUMN(G4:V4),2)=1,G4:V4)) which sums alternate columns (thanks Gord Dibben) These have been a big help. But everything leads to another question. Which is: Can these two be combined so that when I insert two (or N) columns to a sheet, the rolling 6 month totals which add every other (or nth) column are automatically updated as described originally? Thanks again. Wal50 "Ron Coderre" wrote: Glad to help....and thanks for the feedback. *********** Regards, Ron XL2002, WinXP "wal50" wrote: Thanks Ron. That looks like it does the trick. WAL50 "Ron Coderre" wrote: Try something like this: With values in B2:i2 This formula sums the 6 cells immediately before J2 J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) If you insert a column before Col_J, the formula, of course, moves into K2 and automatically adjusts so it sums the 6 cells immediately before K2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "wal50" wrote: The sheet calculates 6mo totals and averages. Each month, a new column is inserted and added to the totals & average, while the one from six months ago is dropped. (column is retained for other reasons, just no longer included in these calculations). Is there a way to do this without having to update the column references for the calculations every month? Thanks in advance, Wal50 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
See if this fits into your plans.....
J2: =SUMPRODUCT((MOD(COLUMN(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)),2)=1)*(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1))) The basic structure is this: =SUMPRODUCT ( Col_Num for each cell in the calculated range is Odd (True=1, False=0) X Each cell in the calculated range ) Does that help? *********** Regards, Ron XL2002, WinXP "wal50" wrote: Ron, J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) resolves one question - thanks again. And experimenting has shown me this can also be used for columns after J by changing to +6 (or +12) and the -1 to +1. What does the "other" 1 control? I also found =SUM(IF(MOD(COLUMN(G4:V4),2)=1,G4:V4)) which sums alternate columns (thanks Gord Dibben) These have been a big help. But everything leads to another question. Which is: Can these two be combined so that when I insert two (or N) columns to a sheet, the rolling 6 month totals which add every other (or nth) column are automatically updated as described originally? Thanks again. Wal50 "Ron Coderre" wrote: Glad to help....and thanks for the feedback. *********** Regards, Ron XL2002, WinXP "wal50" wrote: Thanks Ron. That looks like it does the trick. WAL50 "Ron Coderre" wrote: Try something like this: With values in B2:i2 This formula sums the 6 cells immediately before J2 J2: =SUM(INDEX(2:2,1,COLUMN(J2)-6):INDEX(2:2,1,COLUMN(J2)-1)) If you insert a column before Col_J, the formula, of course, moves into K2 and automatically adjusts so it sums the 6 cells immediately before K2 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "wal50" wrote: The sheet calculates 6mo totals and averages. Each month, a new column is inserted and added to the totals & average, while the one from six months ago is dropped. (column is retained for other reasons, just no longer included in these calculations). Is there a way to do this without having to update the column references for the calculations every month? Thanks in advance, Wal50 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
| ranking query | Excel Discussion (Misc queries) | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| How do I create a rolling average chart, adding most recent data? | Charts and Charting in Excel | |||
| Line Graph Data Recognition | Charts and Charting in Excel | |||
Hybrid Mode
