hello,

I have a problem that is bugging me... hope you have a bit of time to consider the code herebelow. I can figure out how to handle best the problem

I have a worksheet in which I have different inputs.
I use the following function to return 0, 1 or 2 depending on conditions

the inputs are: spot_current, strike, barrier_di and barrier_uo

spot current and strike are always set.
barrier_di and barrier_uo can either not be used at all OR 1 of the two can be used OR both at the same time.

with: barrier_di < strike < barrier_uo (always)

right now it works fine if I don't set barrier_di and barrier_uo
if I set the barriers: the function goes ballistic and returns the wrong results.

does anyone have an idea of the method I have to use to make it right please?

what I am looking for:
if no barrier is set: spot_current < strike returns 0
if barrier_di only is set: spot_current < barrier_di returns 0, 1 otherwise
if barrier_uo only is set: spot_current > barrier_uo returns 2, 1 otherwise
if barrier_di AND barrier_uo are both set: spot_current < barrier_di would return 0, and would return 1 ONLY if spot_current < barrier_uo. if spot_current > barrier_uo result would be 2.

what I currently get is:

no barriers: no problem
barrier_uo only: problem, returns 1 when spot < strike instead of 0
barrier_di only: problem, returns 2 when spot < Barrier_DI instead of 0
barrier_uo and barrier_di at the same time: problem, returns 1 when spot < Barrier_DI instead of 0


here is the code:
[VBA]
Public Function RC_Evaluation(Spot_Current As Variant, Strike As Variant, _
Barrier_DI As Variant, Barrier_UO As Variant) As Integer

RC_Evaluation = 1

If Barrier_DI <> 0 Or Barrier_UO <> 0 Then

RC_Evaluation = IIf(Spot_Current < Barrier_DI, 0, 1)
RC_Evaluation = IIf(Spot_Current > Barrier_UO, 2, 1)

Else

RC_Evaluation = IIf(Spot_Current < Strike, 0, 1)

End If

End Function

[/VBA]


thanks for your attention and for your help!
A.