Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
replace first 4 Endif with Else
"RRyan" wrote: I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
This should work
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("E1:E10")) Is Nothing Then With Target Select Case .Value Case Is <= 365 .Offset(0, 1).Value = "0 Weeks" Case Is <= 730 .Offset(0, 1).Value = "1 Weeks" Case Is <= 1825 .Offset(0, 1).Value = "2 Weeks" Case Is <= 3650 .Offset(0, 1).Value = "3 Weeks" Case Is 3650 .Offset(0, 1).Value = "4 Weeks" End Select End With End If End Sub "RRyan" wrote: I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
I tried to put your suggestion into the function line in Excel with no luck.
I opened the VB editor and put in the folowing: If col(e) <= (365) Then col(f) = "0 weeks" Else if col(e) (360) and <= (720) then col(f) = "1 Week" Else if col (e) (720) and <= (1800) then col(f) = "2 Weeks" Else if col (e) (1800) and <= (3600) then col(f) = "3 Weeks" Else If col(e) (3600) Then col(f) = "4 Weeks" I get an error on the "<=" and I'm wondering if col(e) is the right way to assign the function to colmn E "Atif" wrote: replace first 4 Endif with Else "RRyan" wrote: I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
put this into a standered Module
and use this formula in cell F1 =VactionTime(E1) Function VactionTime(target As Range) As String With target Select Case .value Case Is = 0 VactionTime = "" Case Is <= 365 VactionTime = "0 Weeks" Case Is <= 730 VactionTime = "1 Weeks" Case Is <= 1825 VactionTime = "2 Weeks" Case Is <= 3650 VactionTime = "3 Weeks" Case Is 3650 VactionTime = "4 Weeks" End Select End With End Function "RRyan" wrote: I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
Try using a lookup table instead.
ColA ColB ColC 1 365 0 weeks 366 730 1 week 731 1825 2 weeks 1826 3650 3 weeks 3650 999999 4 weeks (Cell A100) (Cell B100) (Cell C100) Employee Days Employee Weeks of Vaca AA 739 =vlookup(B100,$A$1:$C$5,3,False) "RRyan" wrote: I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple "IF" statements in function
I got it to work with this.
=IF(D5<360,"0",IF(D5<720,"5",IF(D5<1800,"10",IF(D5 <3600,"15",IF(D5=3600,"20",""))))) "RussellT" wrote: Try using a lookup table instead. ColA ColB ColC 1 365 0 weeks 366 730 1 week 731 1825 2 weeks 1826 3650 3 weeks 3650 999999 4 weeks (Cell A100) (Cell B100) (Cell C100) Employee Days Employee Weeks of Vaca AA 739 =vlookup(B100,$A$1:$C$5,3,False) "RRyan" wrote: I'm trying to build a vacation tracker but i don't know VB. Is there a way to get muliple if statements using the function built into excel? This is what I would ike to use: If (e) <= (365) Then (f)= "0 Weeks" End If if (e) (365) and <= (730) then (f) = "1 week" End If if (e) (730) and <= (1825) then (f) = "2 Weeks" End If if (e) (1825) and <= (3650) then (f) = "3 Weeks" End If If (e) (3650) Then (f) = "4 Weeks" End If (e) and (f) are the columns Thanks in advance Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Multiple "IF" statements in a formula | Excel Discussion (Misc queries) | |||
Creating an "If" Function with multiple "if"s | Excel Worksheet Functions | |||
How can I have multiple "If" statements in one formula? | Excel Discussion (Misc queries) | |||
Multiple "IF" statements every cell in range | Excel Programming |