Quarter Calculation

[jerrymoon1]

I have an input spreadsheet that I have monthly data. Row 2 has the month and year - C3 has Jan 05 all the way through AL with Dec 2007. Data is on rows 3 to row 1057. Is there a way to have a formula add the months for a quarter using a date entered in cell B1? For example if I enter

Apr 07 in cell D2 it would add data from cells AD3 and put it in cell CT3
May 07 in cell D2 and it would add cells AD3+AE3 and put it in cell CT3
Jun 07 in cell D2 and it would add cells AD3+AE3+AF3 and put it in cell CT3
Jul 07 in cell D2 and it would add cells AG3and put it in cell CT3....etc.

Would like to be able to do this for the entire range from Jan 05 - Dec 07.

Thanks for your assistance.

 

[Expiry]

There are lots of ways to do this. With a combination of nesting IF formulae (ie. if d1 is less than your latest date then sum these, if d1 is less than your next latest date then sum these, etc(nesting works in order, so if it satisfies the first IF, it doesn't matter if it satisfies the rest too)).

 

[jerrymoon1]

But you are only allowed 7 in a nested if statement. This would be impossible with a three year range I am looking at.

 

[Expiry]

What about if you use a row to give a cumalative total for each quarter underneath the total for each month, so that month1 =month 1, month2 =month 1+2 and month 3=month 1+2+3.

When you've done this for the first quarter you can drag the three celss together across for each quarter after that to save you constantly typing it out.

Then use a hlookup on d1 and c3, but ensure you use the TRUE aspect of hlookup, that way it will look for the exact date you enter and return the cumalative value, but if the exact date isn't there (which it probably won't be unless the date is the last day of the month), then it'll return the value from the nearest date less than that. So, if your d1 date is 3/04/06, it'll return the value for 28/02/06.

 

[jerrymoon1]

That is feasible, but it would double the lines of data that I have in the spreadsheet, and the spreadsheet is several megs in size currently.

 

[Gerald Higgins]

Jerrymoon - I am confused. Do you want to add up just a single row, or all the rows ?

If you want to add up all the rows, this is simple - create totals as described in Expiry's second post - this would only add one more row.

Then use hlookup to cross reference against the month you input.

If you want to add only the months for specific rows, this is more complicated (though it can be done) - please explain more.

 

[jerrymoon1]

Whe I calculate the budget number for a quarter and I want to look at July only, using the formula to add the three months will not work, because all my budget data is preloaded for the year. So when I look at the current quarter for budget it is giving me the total for all three months. Having the budget informaiton preloaded saves me time every month.

 

[Gerald Higgins]

Well that doesn't make it any clearer for me.

Are you able to post a sample of your data, and be very clear about what you want to do ?

 

[barry houdini]

Hello jerry,

Your first post is a bit confusing about the actual locations of the data. Assuming that you have your dates in C2:AL2 and data in the row below in C3:AL3 then you put a specific date in B1

All dates should be 1st of the month in question.

Try this formula in CT3

=SUM(INDEX(C3:AL3,MATCH(B$1,C$2:AL$2,0)-MOD(MONTH(B$1)-1,3)):INDEX(C3:AL3,MATCH(B$1,C$2:AL$2,0)))

edit:

or you might prefer

=SUM(OFFSET(B3,,FLOOR(MATCH(B$1,C$2:AL$2,0)-1,3)+1,,MOD(MONTH(B$1)-1,3)+1))

 

[jerrymoon1]

My spreadsheet is too large to upload.

I have data by month and I would like to be able to select a month and have a formula to add up the quarter for that specific month. For example if I am working on May 2007, I would like to have the Q2 2007 calculated. I have a formula where I add April May and June of 2007 together and this works fine for my actual financial data. I have an issue on the budget side. The data by month for 2007 is preloaded in my template, so my formula for Q2 2007 is showing April, May and June and I would like a formula that would only pick up the first two months. The simple way around this is to load budget data monthly, but it slows me down.