PDA

View Full Version : Creating function with Ifs



alexlus
02-01-2015, 10:00 AM
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
02-01-2015, 10:51 AM
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
02-01-2015, 03:11 PM
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
02-02-2015, 06:54 AM
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!