How can I do a rolling 12 months calculation 1

[Pack10]

We have a spreadsheet where they keep a rolling 12 months of data plus history. They add a column at the end of the sheet and change all the
formulas. It's a pain...They asked me if there was anything that can be done to ease it. What I would like to do is....
Have them add the latest month of data say in F37 and go back 12 columns for the range and add the values. This way, they can add the column, everything would be "pushed" to the right (the history)...
So, he adds in jan-14 info by adding a column...I want to add up the last 12 dec-13 thru Jan-13.

Jan-14 dec-13 Nov-13 Oct13 Sept-13 Aug-13 Jul-13 Jun-13 May-13 Apr-13 Mar-13 Feb-13 Jan-13 Dec-12 Nov-12......
7 8 11 4 3 7 6 10 7 4 5 9 11 8

 

[SkipVought]

hi,

Well you can use the COUNT() function to the number of columns and subtract 11, inside of the OFFSET() function to return the 12 cell range.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Pack10]

Thanks Skip...I had a feeling Offset() would be used.

 

[xlbo]

=sum(offset(f1,counta(f:f)-13,0,12,1))
Untested

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Pack10]

Geoff
Thanks for the reply. Can you quickly
Explain the number attributes
13,0,12,1 and how they pertain
To the function.
Thanks much

 

[xlbo]

F1 is your friend
Offset(
Base position
Rows down to move
Columns across to move
Range depth
Range width
)

Counta(f:f)-13 is the start cell in your Rolling 12 range
0 is the number of columns to move across
12 is the depth of the range
1 is the width of the range

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Pack10]

Thank you so much