• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

12 month rolling total

Z

zvukvnochi

New Member
hello. i need help with creating a formula that will calculate the sum of balances based on a 12 rolling months as of a specified date. for example, as of 03/31/11 i would like a sum of balances for the period of 04/30/10-03/31/11; as of 04/30/11, sum balances 05/31/10-04/30/11, etc.


my data set is set up like this: a1:r1 months headers (a1=jan-10; r1=may-11); a2:r2 contain balances; cell b7 requests the 'as of date'; cell b8 would like to have the sum for the 12 rolling months returned. for example, as of mar-11, return sum for the 12 rolling months 04/30/10-03/31/11.


can someone help with this formula? not really sure where to start...index/sumif??? thanks in advance!!!
 
It'd look something like:


=SUM(OFFSET(INDIRECT(ADDRESS(1,MATCH(B7,A1:R1,0))),1,-11,1,12))


This assumes your headers match the data type in cell B7. Both should be either text or date.


It's an array function, so enter it using Ctrl-Shift-Enter.


You'll need to add checks so that a date less than 12 months from the end of the data can't be entered. I'd suggest a drop list in B7 to keep everything simple.
 
Hi Mike86. Thanks for the tips. I also found out that your formula works without using crtl-shift-enter, too!
 
Fred,


Ah, yeah. The offset's naturally returning an array when the width value is provided. Same thing happens when the height is increased to >1. I'm going to have to remember that.
 
Hi Mike86 thanks for this one


I know the question asked for an a to r range but your formula works even better if we make it more dynamic if we match B7 against Row 1 instead of a1:r1 like this:


=SUM(OFFSET(INDIRECT(ADDRESS(1,MATCH(B7,1:1,0))),1,-11,1,12))
 
You must log in or register to reply here.
Back
Top