Search for fiscal quarter and year

**joodkap** · 05-19-2010, 12:42 PM

I have a spreadsheet that looks at outstanding quarterly data. The current column headings are:

1 - Q4 2007 .... 11 - Q2 2010 12 - Q3 2010 13 - Q4 2010 14 - Q1 2011

The column headings will change as each new quarter is entered, with the new quarter appended to the end, and all previous quarters with no outstanding data removed.

The first column (which is J) will always be fixed, but the last column will depend on the numbers of quarters being shown.

I need to enter a formula to determine all the quarters prior to the current year and quarter. for example, if I have Q4 in cell A1, and this is year 2010, I want to look up in my column headings where Q4 2010 is listed, (in this case it is the heading "13 - Q4 2010"), then sum all the columns from J up to, but not including that one.

**NBVC** · 05-19-2010, 01:21 PM

Are the numbers (1, 2, ... 11, etc) in the headers too?

Assuming they are not, and that you just are indicating to us the column numbers,

then something like:

=SUM(J2:INDEX(J2:N2,MATCH(A$1&" "&YEAR(TODAY()),$J$1:$N$1,0)-1))

this will sum the values in row2 starting from J2 and ending 1 column before the indicated quarter in A1 and current year.

formula can be copied down to get sums of next rows...

See attached.

**joodkap** · 05-21-2010, 09:32 AM

The headings DO include the strings "1- ", "2 - " etc.

The attached spreadsheet lists the actual headings.
Note the formula in cell A2 is =SUM(J2:T2). what I'm trying to do is replace the hard-coded T2 with the cell reference of the quarter previous to the current quarter, shown in B1, taking the current year into consideration.

In this example, the earliest 'Past Due' quarter is Q4 2007, so that is the data in cell J1. However, if that customer pays their bill, the next earliest Past Due quarter will become Q1 2009, and everything will move left accordingly, so the rightmost cell reference will change. (Col J will always be the first column in the range).

Thanks.

**NBVC** · 05-21-2010, 02:41 PM

Try:

Please Login or Register  to view this content.

**joodkap** · 05-21-2010, 03:49 PM

That's it!!!

Thank you so much!

**joodkap** · 06-01-2010, 09:48 AM

I had to modify the formula to return the correct fiscal year: January-May are always in the current fiscal year, and June-December are the next fiscal year.

The following formula replaced YEAR(TODAY) with a LOOKUP referencing a Month/Year lookup table.

=SUM($J89:INDEX($J89:$IV89,MATCH("*"&$D$88&" "&LOOKUP(MONTH(TODAY()),$B$140:$B$151,$C$140:$C$151),$J$88:$IV$88,0)-1))

However, there seems to be a problem with the INDEX... -1, as the sum includes the current quarter, whereas the original formula summed to the prior quarter, which was what I wanted.

Can you tell me what's wrong?

**NBVC** · 06-01-2010, 10:01 AM

Are you sure the LOOKUP() function is returning the result you intended?

Try the lookup on it's own to see what is returned.

the -1 is just to offset the matched column back 1 to sum to previous month.

If still an issue, post sample with issue.

**joodkap** · 06-01-2010, 10:46 AM

Problem solved:

Today is June 1, the first day of the new fiscal year, but I forgot to modify the current month from Q4 to Q1. It is correctly picking up the Q4 2010 data.

I AM DUMB!!!