|
Help With Expanding A Formula
Greetings,
I have a formula the checks cell B2 for 1 of 4 conditions (Monthly, Quarterly, Semi-Annually and Annually). The problem I am having is trying to find the correct month or correct quarter or correct half year to display. Here is the formula: =IF(B2="Monthly",F6,IF(B2="Quarterly",SUM(F6:H6),I F(B2="Semi-Annually",SUM(F6:K6),IF(B2="Annually",SUM(F6:Q6),0 ))) The data I am trying to address is in row 6 on columns F through Q This formula will give me the total for January (F6), 1st quarter (SUM(F6:H6)), first half of the year (SUM(F6:K6) and the whole year (SUM(F6:Q6)). The reference date is in row 4 to be matched with E3. Any suggestions and help would be appreciated. TIA -Minitman |
Is this what you mean?
=IF(B2="Monthly",OFFSET(F6,0,MONTH(TODAY()-1)),IF(B2="Quarterly",SUM(OFFSET( F6,0,INT((MONTH(TODAY())-1)/3)*3,1,3)),IF(B2="Semi-Annually",SUM(OFFSET(F6,0 ,INT((MONTH(TODAY())-1)/6)*6,1,6)),SUM(F6:Q6)))) -- HTH RP (remove nothere from the email address if mailing direct) "Minitman" wrote in message ... Greetings, I have a formula the checks cell B2 for 1 of 4 conditions (Monthly, Quarterly, Semi-Annually and Annually). The problem I am having is trying to find the correct month or correct quarter or correct half year to display. Here is the formula: =IF(B2="Monthly",F6,IF(B2="Quarterly",SUM(F6:H6),I F(B2="Semi-Annually",SUM(F 6:K6),IF(B2="Annually",SUM(F6:Q6),0))) The data I am trying to address is in row 6 on columns F through Q This formula will give me the total for January (F6), 1st quarter (SUM(F6:H6)), first half of the year (SUM(F6:K6) and the whole year (SUM(F6:Q6)). The reference date is in row 4 to be matched with E3. Any suggestions and help would be appreciated. TIA -Minitman |
Hey Bob,
Please excuse my ignorance, but how does this work? -Minitman On Mon, 17 Jan 2005 20:11:09 -0000, "Bob Phillips" wrote: Is this what you mean? =IF(B2="Monthly",OFFSET(F6,0,MONTH(TODAY()-1)),IF(B2="Quarterly",SUM(OFFSET( F6,0,INT((MONTH(TODAY())-1)/3)*3,1,3)),IF(B2="Semi-Annually",SUM(OFFSET(F6,0 ,INT((MONTH(TODAY())-1)/6)*6,1,6)),SUM(F6:Q6)))) |
Well, assuming that it does work (that is, it returns the answer that you
want), the first bit I am sure that you understand, a simple test for the type, that is B2= Monthly OFFSET(F6,0,MONTH(TODAY()-1)) this calculates a month offset from today's date, and offset's into the range F6:Q6 with this number. So Jan will return F6, Feb will return G6, etc. Quarterly SUM(OFFSET(F6,0,INT((MONTH(TODAY())-1)/3)*3,1,3)), first we calculate the quarter offset that today's date is in (INT((MONTH(TODAY())-1)/3)*3, which returns 0 for Jan-Mar, 3 for Apr-Jun etc. This is used with the OFFSET function to get a range within F6:Q6 starting at that offset, for 3 columns Semi-Annually SUM(OFFSET(F6,0,INT((MONTH(TODAY())-1)/6)*6,1,6)), similar to quarterly, but calculates the half-year, and gets a 6 column range Otherwise sums the whole range F6:Q6. To prove it worked, as far as my understanding goes, I replaced TODAY() with A1, and tried various dates in A1. -- HTH RP (remove nothere from the email address if mailing direct) "Minitman" wrote in message ... Hey Bob, Please excuse my ignorance, but how does this work? -Minitman On Mon, 17 Jan 2005 20:11:09 -0000, "Bob Phillips" wrote: Is this what you mean? =IF(B2="Monthly",OFFSET(F6,0,MONTH(TODAY()-1)),IF(B2="Quarterly",SUM(OFFSET ( F6,0,INT((MONTH(TODAY())-1)/3)*3,1,3)),IF(B2="Semi-Annually",SUM(OFFSET(F6, 0 ,INT((MONTH(TODAY())-1)/6)*6,1,6)),SUM(F6:Q6)))) |
Hey Bob,
The monthly and the annually works but the quarterly and the semi-annual doesn't. Rethinking the problem, I decided to add 6 columns to the end of the original 12 (R thru W). I am not sure how to modify the formula to go to them (1st quarter= R6, 2nd quarter=S6, 3rd quarter=T6, 4th quarter=U6, 1st half year=V6 and the 2nd half year=W6). The problem was that if the date was anywhere but in the last month of the quarter or semi-annual time, it gave three or six months worth of figures, just not a legitimate set of figures. Hence the extra columns. I seem to remember something about q being for a quarter and some other letter for semi-annual - I just can't remember where to use them! Any ideas? TIA -Minitman On Mon, 17 Jan 2005 21:20:22 -0000, "Bob Phillips" wrote: Well, assuming that it does work (that is, it returns the answer that you want), the first bit I am sure that you understand, a simple test for the type, that is B2= Monthly OFFSET(F6,0,MONTH(TODAY()-1)) this calculates a month offset from today's date, and offset's into the range F6:Q6 with this number. So Jan will return F6, Feb will return G6, etc. Quarterly SUM(OFFSET(F6,0,INT((MONTH(TODAY())-1)/3)*3,1,3)), first we calculate the quarter offset that today's date is in (INT((MONTH(TODAY())-1)/3)*3, which returns 0 for Jan-Mar, 3 for Apr-Jun etc. This is used with the OFFSET function to get a range within F6:Q6 starting at that offset, for 3 columns Semi-Annually SUM(OFFSET(F6,0,INT((MONTH(TODAY())-1)/6)*6,1,6)), similar to quarterly, but calculates the half-year, and gets a 6 column range Otherwise sums the whole range F6:Q6. To prove it worked, as far as my understanding goes, I replaced TODAY() with A1, and tried various dates in A1. |
You still have the same problem about how to work out the quarter and the
semi-annual. Why not pots some data and expected results, and we'll get it to work. -- HTH RP (remove nothere from the email address if mailing direct) "Minitman" wrote in message ... Hey Bob, The monthly and the annually works but the quarterly and the semi-annual doesn't. Rethinking the problem, I decided to add 6 columns to the end of the original 12 (R thru W). I am not sure how to modify the formula to go to them (1st quarter= R6, 2nd quarter=S6, 3rd quarter=T6, 4th quarter=U6, 1st half year=V6 and the 2nd half year=W6). The problem was that if the date was anywhere but in the last month of the quarter or semi-annual time, it gave three or six months worth of figures, just not a legitimate set of figures. Hence the extra columns. I seem to remember something about q being for a quarter and some other letter for semi-annual - I just can't remember where to use them! Any ideas? TIA -Minitman On Mon, 17 Jan 2005 21:20:22 -0000, "Bob Phillips" wrote: Well, assuming that it does work (that is, it returns the answer that you want), the first bit I am sure that you understand, a simple test for the type, that is B2= Monthly OFFSET(F6,0,MONTH(TODAY()-1)) this calculates a month offset from today's date, and offset's into the range F6:Q6 with this number. So Jan will return F6, Feb will return G6, etc. Quarterly SUM(OFFSET(F6,0,INT((MONTH(TODAY())-1)/3)*3,1,3)), first we calculate the quarter offset that today's date is in (INT((MONTH(TODAY())-1)/3)*3, which returns 0 for Jan-Mar, 3 for Apr-Jun etc. This is used with the OFFSET function to get a range within F6:Q6 starting at that offset, for 3 columns Semi-Annually SUM(OFFSET(F6,0,INT((MONTH(TODAY())-1)/6)*6,1,6)), similar to quarterly, but calculates the half-year, and gets a 6 column range Otherwise sums the whole range F6:Q6. To prove it worked, as far as my understanding goes, I replaced TODAY() with A1, and tried various dates in A1. |
Hey Bob,
It might be easier to send a sample worksheet since this sheet has about 21 columns and trying to write out every thing could get a bit messy, as well as take up the newsgroups bandwidth. I could send it to you off group (as well as anyone else who would like to look at it), if that will work for you? Let me know. Thanks -Minitman On Mon, 17 Jan 2005 22:43:52 -0000, "Bob Phillips" wrote: You still have the same problem about how to work out the quarter and the semi-annual. Why not pots some data and expected results, and we'll get it to work. |
Hey Bob,
It turns out that the answer was in your last solution. I just had to change the starting point and the dividers which makes it look like this: =IF(A2="Monthly", OFFSET(D6,0,MONTH(C3)-1), IF(A2="Quarterly", OFFSET(P6,0,(C4)-1), IF(A2="Semi-Annually", OFFSET(T6,0,(C4)/2), V6))) I removed the spaces and line breaks and it works well. I hadn't thought of using MONTH() or OFFSET() in this way, thank you for the help. -Minitman On Mon, 17 Jan 2005 17:12:39 -0600, Minitman wrote: Hey Bob, It might be easier to send a sample worksheet since this sheet has about 21 columns and trying to write out every thing could get a bit messy, as well as take up the newsgroups bandwidth. I could send it to you off group (as well as anyone else who would like to look at it), if that will work for you? Let me know. Thanks -Minitman On Mon, 17 Jan 2005 22:43:52 -0000, "Bob Phillips" wrote: You still have the same problem about how to work out the quarter and the semi-annual. Why not pots some data and expected results, and we'll get it to work. |
| All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com