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,b10),1,....etc.

how do I make this into a user defined function?

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 :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob Slagle" schrieb im Newsbeitrag
...
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,b10),1,....etc.

how do I make this into a user defined function?

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

=IF(AND(I2=0,E20),1,IF(AND(I2<0,E2=0),1,IF(AND(I2 <0,E20),(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.



"Frank Kabel" wrote in message ...
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 :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob Slagle" schrieb im Newsbeitrag
...
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,b10),1,....etc.

how do I make this into a user defined function?

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

--
Regards,
Tom Ogilvy


"Rob Slagle" wrote in message
m...
here is the formula that I want to make into a function in VBA


=IF(AND(I2=0,E20),1,IF(AND(I2<0,E2=0),1,IF(AND(I2 <0,E20),(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.



"Frank Kabel" wrote in message
...
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 :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob Slagle" schrieb im Newsbeitrag
...
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,b10),1,....etc.

how do I make this into a user defined function?