Rolling data

W

wal50

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
 
R

Ron Coderre

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
 
R

Ron Coderre

Glad to help....and thanks for the feedback.

***********
Regards,
Ron

XL2002, WinXP
 
W

wal50

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
 
R

Ron Coderre

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
 
W

wal50

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
 
R

Ron Coderre

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
 
W

wal50

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 said:
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 said:
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
Click to expand...
Click to expand...
 
W

wal50

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 said:
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 said:
I think it's typed properly.
Click to expand...
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 said:
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


:

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


:

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


:

Glad to help....and thanks for the feedback.

***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron. That looks like it does the trick.
WAL50

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Top