Help With Expanding A Formula

[Minitman]

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),IF(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

 

[Bob Phillips]

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]

Hey Bob,

Please excuse my ignorance, but how does this work?

-Minitman

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

 

[Minitman]

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