changing nested if statements into a user defined function

[Rob Slagle]

I have several financial worksheets where I am figuring a percent change
with a nested if(and statements.

c1 =if(and(a1=0,b1<0),-1,if(and(a1=0,b1>0),1,....etc.

how do I make this into a user defined function?

 

[Frank Kabel]

Hi
depends on your conditions. But in most case no UDF required but simply
applying a different alorithm or using a lookup table should work. So
you may post your complete conditions

 

[Rob Slagle]

here is the formula that I want to make into a function in VBA

=IF(AND(I2=0,E2>0),1,IF(AND(I2<0,E2=0),1,IF(AND(I2<0,E2>0),(K2/I2)*-1,IF(AND(I2=0,E2=0),0,IF(AND(I2=0,E2<0),-1,IF(AND(I2<0,E2<0),(K2/I2)*-1,K2/I2))))))

I2 is the prior year number.
E2 is the current year number.
K2 is the change amount E2-I2.

 

[Tom Ogilvy]

Place this in a general module.

Public Function CalcVal(rng1 As Range, rng2 As Range, rng3 As Range)
Dim I2, E2, K2
I2 = rng1.Value
E2 = rng2.Value
K2 = rng3.Value

Select Case True
Case (I2 = 0 And E2 > 0)
CalcVal = 1
Case (I2 < 0 And E2 = 0)
CalcVal = 1
Case (I2 < 0 And E2 > 0)
CalcVal = (K2 / I2) * -1
Case (I2 = 0 And E2 = 0)
CalcVal = 0
Case (I2 = 0 And E2 < 0)
CalcVal = -1
Case (I2 < 0 And E2 < 0)
CalcVal = (K2 / I2) * -1
Case Else
CalcVal = K2 / I2
End Select
End Function