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

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" <exreply@i-m-pNOSPAM.net> wrote in message
news:tr2ou0p9mk8mesus54brcnqc0a0v03gdg6@4ax.com...
> 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(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"
<bob.phillips@notheretiscali.co.uk> 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" <exreply@i-m-pNOSPAM.net> wrote in message
news:ou8ou0d7cda6483qkip7n3jham70pnb3nd@4ax.com...
> Hey Bob,
>
> Please excuse my ignorance, but how does this work?
>
> -Minitman
>
> On Mon, 17 Jan 2005 20:11:09 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> 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"
<bob.phillips@notheretiscali.co.uk> 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" <exreply@i-m-pNOSPAM.net> wrote in message
news:v0fou09097ei5001emo4gpr58450nq0aal@4ax.com...
> 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"
> <bob.phillips@notheretiscali.co.uk> 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"
<bob.phillips@notheretiscali.co.uk> 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 <exreply@i-m-pNOSPAM.net>
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"
><bob.phillips@notheretiscali.co.uk> 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.