Eliminating Multiple IF/THEN loops

I have a table of data with dates in the top row(weekly). I need to take an
action depending on which quarter of the year the date is in. What I am
working on is assigning a number 1 thru 4 based on evaluation of the date, ie
If Month(N4)<4 Then
Qtr= 1
Else if Month (N4)>3 AND Month (N4) <7 Then
Qtr = 2......

From this I would then have something like

If Qtr = 1 then
evaulate cell

I believe there is a cleaner way to do this instead of multiple if
statements, if any could provide insight it would be appreciated.
--
Thanks in advance,
JC

You can calculate the Qtr value:

Qtr=INT((Month(N4)-1)/3)+1

Does this help?

You could then use Select CASE instead "If" statements to determine action
on value of Qtr

"JC" wrote:

> I have a table of data with dates in the top row(weekly). I need to take an
> action depending on which quarter of the year the date is in. What I am
> working on is assigning a number 1 thru 4 based on evaluation of the date, ie
> If Month(N4)<4 Then
> Qtr= 1
> Else if Month (N4)>3 AND Month (N4) <7 Then
> Qtr = 2......
>
> From this I would then have something like
>
> If Qtr = 1 then
> evaulate cell
>
> I believe there is a cleaner way to do this instead of multiple if
> statements, if any could provide insight it would be appreciated.
> --
> Thanks in advance,
> JC

Probably a select case statement

select Month(N4)
case <= 3
Qtr = 1
case <= 6
Qtr = 2
case <= 9
Qtr = 3
case <= 12
Qtr = 4
case else
msgbox "Error"
end select
--
HTH...

Jim Thomlinson


"JC" wrote:

> I have a table of data with dates in the top row(weekly). I need to take an
> action depending on which quarter of the year the date is in. What I am
> working on is assigning a number 1 thru 4 based on evaluation of the date, ie
> If Month(N4)<4 Then
> Qtr= 1
> Else if Month (N4)>3 AND Month (N4) <7 Then
> Qtr = 2......
>
> From this I would then have something like
>
> If Qtr = 1 then
> evaulate cell
>
> I believe there is a cleaner way to do this instead of multiple if
> statements, if any could provide insight it would be appreciated.
> --
> Thanks in advance,
> JC

JC wrote:
> I have a table of data with dates in the top row(weekly). I need to take an
> action depending on which quarter of the year the date is in. What I am
> working on is assigning a number 1 thru 4 based on evaluation of the date, ie
> If Month(N4)<4 Then
> Qtr= 1
> Else if Month (N4)>3 AND Month (N4) <7 Then
> Qtr = 2......
>
> From this I would then have something like
>
> If Qtr = 1 then
> evaulate cell
>
> I believe there is a cleaner way to do this instead of multiple if
> statements, if any could provide insight it would be appreciated.
-------------

Qtr = 1+INT((Month(N4)-1)/3)

Bill

Another thought might be to use the format function which can format the
quarter (why this only exist on the VB side and not the Excel side is a
mystery...)

Qtr = CInt(format(N4, "q"))
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Probably a select case statement
>
> select Month(N4)
> case <= 3
> Qtr = 1
> case <= 6
> Qtr = 2
> case <= 9
> Qtr = 3
> case <= 12
> Qtr = 4
> case else
> msgbox "Error"
> end select
> --
> HTH...
>
> Jim Thomlinson
>
>
> "JC" wrote:
>
> > I have a table of data with dates in the top row(weekly). I need to take an
> > action depending on which quarter of the year the date is in. What I am
> > working on is assigning a number 1 thru 4 based on evaluation of the date, ie
> > If Month(N4)<4 Then
> > Qtr= 1
> > Else if Month (N4)>3 AND Month (N4) <7 Then
> > Qtr = 2......
> >
> > From this I would then have something like
> >
> > If Qtr = 1 then
> > evaulate cell
> >
> > I believe there is a cleaner way to do this instead of multiple if
> > statements, if any could provide insight it would be appreciated.
> > --
> > Thanks in advance,
> > JC