Solved: problem of structure

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.

can you post examplse showing all conditions and results.

I think the spreadsheet formula
=IF(uo>0,(uo<current)+(di<current),--NOT(current<di))
will return your result, but I'm not sure

By "not set", does that mean <>0?
Are negative numbers an issue?

Not sure if I got your logic right, and I used -1 as a flag, but it's easy enougth to modify

[vba]

'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.

Public Function RC_Evaluation(Spot_Current As Variant, _
Strike As Variant, _
Optional Barrier_DI As Variant = -1, _
Optional Barrier_UO As Variant = -1) As Integer

RC_Evaluation = 1

'none set
If Barrier_DI = -1 And Barrier_UO = -1 Then

If Spot_Current < Strike Then RC_Evaluation = 0

'UO set, and DI not
ElseIf Barrier_DI = -1 And Barrier_UO <> -1 Then

If Spot_Current > Barrier_UO Then RC_Evaluation = 2

'DI set, and UO not
ElseIf Barrier_DI <> -1 And Barrier_UO = -1 Then

If Spot_Current < Barrier_DI Then RC_Evaluation = 2

'both set
Else
If Spot_Current < Barrier_DI Then
RC_Evaluation = 0
ElseIf Spot_Current > Barrier_UO Then
RC_Evaluation = 2
End If

End If

End Function

[/vba]

Paul

hello Paul, thanks for taking of your time to look into my problem.

I had to tweak it a bit to fit into my simulation and it seems to work fine :)

here is the final version:

[VBA]Public Function RC_Evaluation(Spot_Current As Variant, _
Strike As Variant, _
Optional Barrier_DI As Variant, _
Optional Barrier_UO As Variant) As Integer

RC_Evaluation = 1

'none set
If Barrier_DI = 0 And Barrier_UO = 0 Then

If Spot_Current < Strike Then RC_Evaluation = 0

'UO set, and DI not
ElseIf Barrier_DI = 0 And Barrier_UO <> 0 Then

If Spot_Current > Barrier_UO Then RC_Evaluation = 2
If Spot_Current < Strike Then RC_Evaluation = 0

'DI set, and UO not
ElseIf Barrier_DI <> 0 And Barrier_UO = 0 Then

If Spot_Current < Barrier_DI Then RC_Evaluation = 0

'both set
Else
If Spot_Current < Barrier_DI Then
RC_Evaluation = 0
ElseIf Spot_Current > Barrier_UO Then
RC_Evaluation = 2
End If

End If

End Function

[/VBA]

Just for interest, I re-wrote in a manner that I think makes it clearer to see what is happening. As I say just FYI

[vba]

Public Enum EVAL_TRISTATE
NO_BARRIER = 0
BARRIER_ONE = 1
BARRIER_TWO
End Enum

Public Function RC_Evaluation(Spot_Current As Variant, _
Strike As Variant, _
Optional Barrier_DI As Variant, _
Optional Barrier_UO As Variant) As EVAL_TRISTATE

RC_Evaluation = BARRIER_ONE

'none set
Select Case True

Case Barrier_DI = 0 And Barrier_UO = 0 And Spot_Current < Strike
RC_Evaluation = NO_BARRIER

'UO set, and DI not
Case Barrier_DI = 0 And Barrier_UO <> 0 And Spot_Current > Barrier_UO
RC_Evaluation = BARRIER_TWO

Case Barrier_DI = 0 And Barrier_UO <> 0 And Spot_Current < Strike
RC_Evaluation = NO_BARRIER

'DI set, and UO not
Case Barrier_DI <> 0 And Barrier_UO = 0 And Spot_Current < Barrier_DI
RC_Evaluation = NO_BARRIER

Case Spot_Current < Barrier_DI
RC_Evaluation = NO_BARRIER

Case Spot_Current > Barrier_UO
RC_Evaluation = BARRIER_TWO
End Select

End Function
[/vba]