| 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Thanks for the response Ron.
I enter =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))) (control/shift/enter) and the formula multiples the sixth column to the right by 3. I think it's typed properly. When a new column is inserted, it again counts 6 to the right and *3. Any ideas? Thanks again. Wal50 "Ron Coderre" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
I think it's typed properly.
Nope! :) Your formula: =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))) Should be this: =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))) There are 4 "INDEX" formulas, not 3. Also: That is NOT an array formula, so commit it with just [enter]. Does that help? *********** Regards, Ron XL2002, WinXP "wal50" wrote: Thanks for the response Ron. I enter =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))) (control/shift/enter) and the formula multiples the sixth column to the right by 3. I think it's typed properly. When a new column is inserted, it again counts 6 to the right and *3. Any ideas? Thanks again. Wal50 "Ron Coderre" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Yes it does.
When I printed your reply, the formula went across the page and got chopped after the first two "index". Incredible. As is this site. Many thanks wal50 "Ron Coderre" wrote: I think it's typed properly. Nope! :) Your formula: =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))) Should be this: =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))) There are 4 "INDEX" formulas, not 3. Also: That is NOT an array formula, so commit it with just [enter]. Does that help? *********** Regards, Ron XL2002, WinXP "wal50" wrote: Thanks for the response Ron. I enter =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))) (control/shift/enter) and the formula multiples the sixth column to the right by 3. I think it's typed properly. When a new column is inserted, it again counts 6 to the right and *3. Any ideas? Thanks again. Wal50 "Ron Coderre" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Rolling data
Hate to be a pest, but this is so useful and learning something is always
good. When I try to modify your formula to sum alternate rows (above) I tried: =SUMPRODUCT((MOD(ROW(INDEX(D:D,1,ROW(D16)-6):INDEX(D:D,1,ROW(D16)-1)),2)=1)*(INDEX(D:D,1,ROW(D16)-6)):(INDEX(D:D,1,ROW(D16)-1))) I changed the function (COLUMN to ROW), the row references (2:2 to D:D), and changed all "+" to "-" to sum rows above. It returns #Ref! What did I do wrong? Wal50 "wal50" wrote: Yes it does. When I printed your reply, the formula went across the page and got chopped after the first two "index". Incredible. As is this site. Many thanks wal50 "Ron Coderre" wrote: I think it's typed properly. Nope! :) Your formula: =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))) Should be this: =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))) There are 4 "INDEX" formulas, not 3. Also: That is NOT an array formula, so commit it with just [enter]. Does that help? *********** Regards, Ron XL2002, WinXP "wal50" wrote: Thanks for the response Ron. I enter =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))) (control/shift/enter) and the formula multiples the sixth column to the right by 3. I think it's typed properly. When a new column is inserted, it again counts 6 to the right and *3. Any ideas? Thanks again. Wal50 "Ron Coderre" wrote: 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 | |||
Linear Mode
