Creating function with Ifs

[alexlus]

Hello,
I'm new to the forum as well as creating functions in excels or any VBA.

I have this set of equations that I want to get a final value based on some If statements. The final value Qr is the output based on variables Pr, Pbh, Ql, Pbp that will be in separate cells.


IF Pr <= Pbp
Qm = Ql / ( 1 - 0.2 * ( Pbh / Pr ) - 0.8 * ( Pbh / Pr ) ^ 2 )
Qr = Qm * ( 1 - 0.2 * ( Pbh / Pr ) - 0.8 * ( Pbh / Pr ) ^2)
ELSE
IF Pbh >= Pbp
J = Ql / ( Pr - Pbh )
ELSE
J = Ql / ( ( Pr - Pbp ) + ( Pbp / 1.8 ) * ( 1 - 0.2 * ( Pbh / Pbp ) - 0.8 * ( Pbh / Pbp ) ^ 2 )
Qbp = J * ( Pr - Pbp )
IF Pbh >= Pbp
Qr = J * ( Pr - Pbh )
ELSE
Qr = Qbp + ( J * ( Pbp / 1.8 ) * ( 1 - 0.2 * ( Pbh / Pbp ) - 0.8 * ( Pbh / Pbp ) ^ 2)



Any help is would be very appreciated.

Thank you.

[Bob Phillips]

Create a UDF

Public Function Qr(Pr, Pbh, Ql, Pbp) As Double
Dim J As Double, Qm As Double, Qbp As Double
    If Pr <= Pbp Then
        Qm = Ql / (1 - 0.2 * (Pbh / Pr) - 0.8 * (Pbh / Pr) ^ 2)
        Qr = Qm * (1 - 0.2 * (Pbh / Pr) - 0.8 * (Pbh / Pr) ^ 2)
    ElseIf Pbh >= Pbp Then
        J = Ql / (Pr - Pbh)
    Else
        J = Ql / (Pr - Pbp) + (Pbp / 1.8) * (1 - 0.2 * (Pbh / Pbp) - 0.8 * (Pbh / Pbp) ^ 2)
        Qbp = J * (Pr - Pbp)
        If Pbh >= Pbp Then
            Qr = J * (Pr - Pbh)
        Else
            Qr = Qbp + (J * (Pbp / 1.8) * (1 - 0.2 * (Pbh / Pbp) - 0.8 * (Pbh / Pbp) ^ 2))
        End If
    End If
End Function

[snb]

It looks as if

Qm = Ql / ( 1 - 0.2 * ( Pbh / Pr ) - 0.8 * ( Pbh / Pr ) ^ 2 )
Qr = Qm * ( 1 - 0.2 * ( Pbh / Pr ) - 0.8 * ( Pbh / Pr ) ^2)

is equivalent to:

Qr=Ql

[alexlus]

Thank you xld for your help.

Snd, you would be correct that Qr=Ql in this instance, There is a varying P that I had to add to make it work, just didn't know the format to make it a function.

Thanks again!