Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
I originally was going to do this just as a standard (albeit long) function
in Excel, but quickly had more nested levels then allowed. So my hope is that I can in VBA produce a custom function that will do all of the work and be compatible with 2003 xls format. Please let me know if this is possible and if there is a better approach to this. Below is the VBA that I have so far, that obviously does not work Im not sure how to use/insert the excel IF statements. Thanks for ANY help. Public Function GroundCover() 'Create Variables to hold numbers Dim input1 As Double Dim input2 As Double Dim input3 As Double 'Fill the variables with input from the worksheet input1 = Worksheet.cels(27, 2) input2 = Worksheet.Cells(27, 3) input3 = Worksheet.Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then 'Use IF statement to check input2 and multiply input3 by coresponding number Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625, IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278, IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156, IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039, IF(input2=30,input3*0.0025,"0")))))))))) Else 'Use second IF statement to check input1 and multiply input3 by coresponding number Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, IF(input2=72,input3*0.0278,"0"))))))))))))) End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
Sorry, the word wrap here kinda makes it hard(er) to read/understand...
"Nick VL" wrote: I originally was going to do this just as a standard (albeit long) function in Excel, but quickly had more nested levels then allowed. So my hope is that I can in VBA produce a custom function that will do all of the work and be compatible with 2003 xls format. Please let me know if this is possible and if there is a better approach to this. Below is the VBA that I have so far, that obviously does not work Im not sure how to use/insert the excel IF statements. Thanks for ANY help. Public Function GroundCover() 'Create Variables to hold numbers Dim input1 As Double Dim input2 As Double Dim input3 As Double 'Fill the variables with input from the worksheet input1 = Worksheet.cels(27, 2) input2 = Worksheet.Cells(27, 3) input3 = Worksheet.Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then 'Use IF statement to check input2 and multiply input3 by coresponding number Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625, IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278, IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156, IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039, IF(input2=30,input3*0.0025,"0")))))))))) Else 'Use second IF statement to check input1 and multiply input3 by coresponding number Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, IF(input2=72,input3*0.0278,"0"))))))))))))) End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
Hi
Look at this: (I will leave it to you to rewrite your second IF statement.) Public Function GroundCover(Input1 As String, Input2 As Integer, Input3 As Double) ' Determine if input1 = flats If Input1 = "Flats" Then 'Use Select Case input2 and multiply input3 by coresponding Number Select Case Input2 Case Is = 4 Output = Input3 * 0.1406 Case Is = 6 Output = Input3 * 0.0625 Case Is = 8 Output = Input3 * 0.0351 Case Is = 9 Output = Input3 * 0.0278 Case Is = 10 Output = Input3 * 0.0225 Case Is = 12 Output = Input3 * 0.0156 Case Is = 18 Output = Input3 * 0.0087 Case Is = 24 Output = Input3 * 0.0039 Case Is = 30 Output = Input3 * 0.0025 Case Else Output = 0 End Select Else 'Use second IF statement to check input1 and multiply input3 by coresponding Number 'Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, 'IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, 'IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, 'IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, 'IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, 'IF(input2=72,input3*0.0278,"0"))))))))))))) End If GroundCover = Output End Function Regards, Per On 9 Okt., 00:21, Nick VL wrote: I originally was going to do this just as a standard (albeit long) function in Excel, but quickly had more nested levels then allowed. *So my hope is that I can in VBA produce a custom function that will do all of the work and be compatible with 2003 xls format. *Please let me know if this is possible and if there is a better approach to this. *Below is the VBA that I have so far, that obviously does not work Im not sure how to use/insert the excel IF statements. *Thanks for ANY help. Public Function GroundCover() 'Create Variables to hold numbers Dim input1 As Double Dim input2 As Double Dim input3 As Double 'Fill the variables with input from the worksheet input1 = Worksheet.cels(27, 2) input2 = Worksheet.Cells(27, 3) input3 = Worksheet.Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then * * 'Use IF statement to check input2 and multiply input3 by coresponding number * * Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625, IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278, IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156, IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039, IF(input2=30,input3*0.0025,"0")))))))))) Else * * 'Use second IF statement to check input1 and multiply input3 by coresponding number * * Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, IF(input2=72,input3*0.0278,"0"))))))))))))) End If End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
Thank you. This seems to work. Here's the final function if anyone cares:
Public Function GroundCover(Input1 As String, Input2 As Integer, Input3 As Double) ' Determine if input1 = flats If Input1 = "Flats" Then 'Use Select Case input2 and multiply 'input3 by coresponding Number Select Case Input2 Case Is = 4 Output = Input3 * 0.1406 Case Is = 6 Output = Input3 * 0.0625 Case Is = 8 Output = Input3 * 0.0351 Case Is = 9 Output = Input3 * 0.0278 Case Is = 10 Output = Input3 * 0.0225 Case Is = 12 Output = Input3 * 0.0156 Case Is = 18 Output = Input3 * 0.0087 Case Is = 24 Output = Input3 * 0.0039 Case Is = 30 Output = Input3 * 0.0025 Case Else Output = "-" End Select Else 'Use second IF statement to check input1 'and multiply input3 by coresponding Number If Not Input1 = "Flats" Then 'Use Select Case input2 and multiply input3 'by coresponding Number Select Case Input2 Case Is = 4 Output = Input3 * 9 Case Is = 6 Output = Input3 * 4 Case Is = 8 Output = Input3 * 2.25 Case Is = 9 Output = Input3 * 1.78 Case Is = 10 Output = Input3 * 1.44 Case Is = 12 Output = Input3 * 1 Case Is = 16 Output = Input3 * 0.56 Case Is = 18 Output = Input3 * 0.45 Case Is = 24 Output = Input3 * 0.25 Case Is = 30 Output = Input3 * 0.16 Case Is = 36 Output = Input3 * 0.1111 Case Is = 48 Output = Input3 * 0.0625 Case Is = 72 Output = Input3 * 0.0278 Case Else Output = "-" End Select End If GroundCover = Output End If GroundCover = Output End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
You could use a lookup table to get around the nesting limit. If you set up
a table like this that contain your multiplication factors for each value of Input 2 (lets say this table is in Sheet3!A1:C13): 4 0.1406 9 6 0.0625 4 8 0.0351 2.25 9 0.0278 1.78 10 0.0225 1.44 12 0.0156 1 16 0 0.56 18 0.0087 0.45 24 0.0039 0.25 30 0.0025 0.16 36 0 0.1111 48 0 0.0625 72 0 0.0278 and Input1 is in B27, Input2 is C27, and input3 is D27, I think this will do what you need: =IF(ISNUMBER(MATCH(C27,Sheet3!A1:A13,0)),D27*VLOOK UP(C27,Sheet3!A1:C13,IF(B27="Flats",2,3),0),0) "Nick VL" wrote: I originally was going to do this just as a standard (albeit long) function in Excel, but quickly had more nested levels then allowed. So my hope is that I can in VBA produce a custom function that will do all of the work and be compatible with 2003 xls format. Please let me know if this is possible and if there is a better approach to this. Below is the VBA that I have so far, that obviously does not work Im not sure how to use/insert the excel IF statements. Thanks for ANY help. Public Function GroundCover() 'Create Variables to hold numbers Dim input1 As Double Dim input2 As Double Dim input3 As Double 'Fill the variables with input from the worksheet input1 = Worksheet.cels(27, 2) input2 = Worksheet.Cells(27, 3) input3 = Worksheet.Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then 'Use IF statement to check input2 and multiply input3 by coresponding number Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625, IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278, IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156, IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039, IF(input2=30,input3*0.0025,"0")))))))))) Else 'Use second IF statement to check input1 and multiply input3 by coresponding number Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, IF(input2=72,input3*0.0278,"0"))))))))))))) End If End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
Thanks both of you.
"JMB" wrote: You could use a lookup table to get around the nesting limit. If you set up a table like this that contain your multiplication factors for each value of Input 2 (lets say this table is in Sheet3!A1:C13): 4 0.1406 9 6 0.0625 4 8 0.0351 2.25 9 0.0278 1.78 10 0.0225 1.44 12 0.0156 1 16 0 0.56 18 0.0087 0.45 24 0.0039 0.25 30 0.0025 0.16 36 0 0.1111 48 0 0.0625 72 0 0.0278 and Input1 is in B27, Input2 is C27, and input3 is D27, I think this will do what you need: =IF(ISNUMBER(MATCH(C27,Sheet3!A1:A13,0)),D27*VLOOK UP(C27,Sheet3!A1:C13,IF(B27="Flats",2,3),0),0) "Nick VL" wrote: I originally was going to do this just as a standard (albeit long) function in Excel, but quickly had more nested levels then allowed. So my hope is that I can in VBA produce a custom function that will do all of the work and be compatible with 2003 xls format. Please let me know if this is possible and if there is a better approach to this. Below is the VBA that I have so far, that obviously does not work Im not sure how to use/insert the excel IF statements. Thanks for ANY help. Public Function GroundCover() 'Create Variables to hold numbers Dim input1 As Double Dim input2 As Double Dim input3 As Double 'Fill the variables with input from the worksheet input1 = Worksheet.cels(27, 2) input2 = Worksheet.Cells(27, 3) input3 = Worksheet.Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then 'Use IF statement to check input2 and multiply input3 by coresponding number Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625, IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278, IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156, IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039, IF(input2=30,input3*0.0025,"0")))))))))) Else 'Use second IF statement to check input1 and multiply input3 by coresponding number Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, IF(input2=72,input3*0.0278,"0"))))))))))))) End If End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
Nick VL,
Within VBA is a statement titled Select Case. For complicated sequences such as you want it is easier to construct and document. I rewrote your function as a macro you can run from within an Excel worksheet. It looks bigger and more complicated because it spreads the function out over many lines, however, that makes it much easier to write and debug. For an Excel worksheet a subroutine forms the basis for a macro. I named it the same as you named your function. The way it works is for the user to first select a cell where they answer is to be placed. You then select the run macro entry from the tools\macro menu item. Then run macro GroundCover. The macro routine uses the three cells to fetch the data then calculates the desired result and finally places that result in the cell you select initially. You can select any cell on the sheet. If your not familiar with the Select Case statement you can go to the Visual Basic window and use help to get a description. The short answer is that you enter a value or expression to be evaluated after the Select Case statement. Following that are a series of case descriptions in the form of expressions. After each case expression is one or more statements to be executed if the condition of the case is met. For the routine shown, each input value is the case being selected. The statement after each case option defines the calculation of your result. All other cases are then ignored and the procedure jumps to the End Select. Two sets of Select Case are used, one for the situation where "Flats" is entered and a second for any other situation. It is feasible and reasonable to use a Select Case for the cell contents that has the word "Flats" also in which case you could have more than two options. To use this in your copy of Excel, copy the entire subroutine into the worksheet VB code window for your worksheet. When you select the macro window on the spreadsheet, the macro GroundCover will show up ready for use. Hope this longwinded answer helps. RDF Public Sub GroundCover() 'Visual Basic creates variables directly from code 'Fill the variables with input from the worksheet With Worksheets("Sheet1") ' when doing multiple actions on a worksheet, use the With .... End Width construct input1 = .Cells(27, 2) ' use .cells function to access worksheet data input2 = .Cells(27, 3) input3 = .Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then ' use the Select Case construct instead of the nested if.. VBA code options are much richer than EXCEL functions Select Case input2 Case Is = 4 Ground_cover = input3 * 0.1406 Case Is = 6 Ground_cover = input3 * 0.0625 Case Is = 8 Ground_cover = input3 * 0.0351 Case Is = 9 Ground_cover = input3 * 0.0278 Case Is = 10 Ground_cover = input3 * 0.0225 Case Is = 12 Ground_cover = input3 * 0.0156 Case Is = 18 Ground_cover = input3 * 0.0087 Case Is = 24 Ground_cover = input3 * 0.0025 Case Is = 30 Ground_cover = input3 * 0.0025 Case Else Ground_cover = 0 'probably should be numeric instead of text... was "0" End Select Else 'Use second IF statement to check input1 and multiply input3 by 'coresponding Number Select Case input2 Case Is = 4 Ground_cover = input3 * 9 Case Is = 6 Ground_cover = input3 * 4 Case Is = 8 Ground_cover = input3 * 2.25 Case Is = 9 Ground_cover = input3 * 1.78 Case Is = 10 Ground_cover = input3 * 1.44 Case Is = 12 Ground_cover = input3 Case Is = 16 Ground_cover = input3 * 0.56 Case Is = 18 Ground_cover = input3 * 0.45 Case Is = 24 Ground_cover = input3 * 0.25 Case Is = 30 Ground_cover = input3 * 0.16 Case Is = 36 Ground_cover = input3 * 0.1111 Case Is = 48 Ground_cover = input3 * 0.0625 Case Is = 72 Ground_cover = input3 * 0.0278 Case Else Ground_cover = 0 'probably should be numeric instead of text... was "0" End Select End If ' return result to the selected cell Selection.Value = Ground_cover End With End Sub "Nick VL" wrote in message ... I originally was going to do this just as a standard (albeit long) function in Excel, but quickly had more nested levels then allowed. So my hope is that I can in VBA produce a custom function that will do all of the work and be compatible with 2003 xls format. Please let me know if this is possible and if there is a better approach to this. Below is the VBA that I have so far, that obviously does not work Im not sure how to use/insert the excel IF statements. Thanks for ANY help. Public Function GroundCover() 'Create Variables to hold numbers Dim input1 As Double Dim input2 As Double Dim input3 As Double 'Fill the variables with input from the worksheet input1 = Worksheet.cels(27, 2) input2 = Worksheet.Cells(27, 3) input3 = Worksheet.Cells(27, 4) ' Determine if input1 = flats If input1 = "Flats" Then 'Use IF statement to check input2 and multiply input3 by coresponding number Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625, IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278, IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156, IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039, IF(input2=30,input3*0.0025,"0")))))))))) Else 'Use second IF statement to check input1 and multiply input3 by coresponding number Output=(IF(input2=4,input3*9, IF(input2=6,input3*4, IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44, IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45, IF(input2=24,input3*0.25, IF(input2=30,input3*0.16, IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625, IF(input2=72,input3*0.0278,"0"))))))))))))) End If End Function |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with IF... Then... Else statement
Thank you. This is similar to the function I ended up building with help
from Per Jessen above. I will keep this in mind for a possible macro in a different area of the spreadsheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions |