| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Eliminating Multiple IF/THEN loops
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Eliminating Multiple IF/THEN loops
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Eliminating Multiple IF/THEN loops
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Eliminating Multiple IF/THEN loops
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| loops???? | Excel Worksheet Functions | |||
| Rename multiple csv files eliminating (-) hyphens from the label. | Excel Discussion (Misc queries) | |||
| Multiple test loops | Excel Programming | |||
| Multiple loops one result | Excel Programming | |||
| macro that loops to multiple columns | Excel Programming | |||
![JC[_11_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
