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
|