UDF Reihenfolge Übergabeparamter führt zu #WERT
24.09.2018 16:50:26
David
Hallo Daniel,
danke für den Hinweis, das habe ich nun korrigiert und alle explizit als Double deklariert. Leider ändert das nichts am Ergebnis. Hier mal die UDF, also Übergabe-Parameter sind alle Gleitkommazahlen von 0 - offen und "-".
Public Function ConfidenceLevelwithRW(relativcl As Double, origworst As Double, origmostlikely _
As Double, origbest As Double, riskfactor As Double) As Double
'This function calculates the absolute risk weighted(1 input paramter - risk factor) value for _
a searched relative confidence level (2 input parameter)
'based on a triangular distribution defined by the values origworst, Most Likely and Best (3,4 _
and 5 input parameters in this order).
'If no risk factor is given (-), then the value 1 is assumed for in the following formulas, _
which then corresponds to the unweighted one.
If riskfactor = "-" Then
riskfactor = 1
End If
'Case distinction, depending on which side of the triangle distribution the required confidence _
level must lie
'If all three input values match, then this value is also assumed to be the confidence level.
If origworst - origmostlikely = 0 And origmostlikely - origbest = 0 Then
ConfidenceLevelwithRW = origmostlikely * riskfactor
Else
Dim clmostlikely As Double
'Determination of the Conficence Level at the value "Most Likely" for the further _
calculation
clmostlikely = (origmostlikely - origworst) / (origbest - origworst)
End If
'The required confidence level is greater than CL"Most Likely", so the value must be on the _
left side of the triangle.
If (1 - relativcl) clmostlikely Then
ConfidenceLevelwithRW = (origbest - (relativcl * (origbest - origworst) * (origbest - _
origmostlikely)) ^ (1 / 2)) * riskfactor
'The required confidence level matches CL"Most Likely"
ElseIf relativcl = clmostlikely Then
ConfidenceLevelwithRW = origmostlikely * riskfactor
End If
End Function