UDF Reihenfolge Übergabeparamter führt zu #WERT

Hallo,
folgendes Problem, das für mich nicht nachvollziehbar ist.
Ich habe eine UDF mit dem Header


Public Function ConfidenceLevelwithRW(riskfactor, relativcl, origworst, origmostlikely, origbest As Double) As Double

,rufe diese in einer Zelle auf mit


= ConfidenceLevelwithRW($L17;$N$10;$D17;$E17;$F17)

und erhalte den erwünschten Wert, das hat lange Problemlos geklappt.
Jetzt war der Wunsch, auf Grund der Übersicht den ersten Übergabewart als letzen zu setzen also Public Function ConfidenceLevelwithRW(relativcl, origworst, origmostlikely, origbest, riskfactor As Double) As Double
Es wird wieder entsprechend mit angepassten Übergabeparamter aufgerufen


= ConfidenceLevelwithRW($N$10;$D17;$E17;$F17;$L17)

und ich erhalte #WERT.
Wie kann das sein? Es hat sich doch quasi nichts geändert?

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

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

Public Function ConfidenceLevelwithRW(relativcl As Double, origworst As Double, origmostlikely _ _ As Double, origbest As Double, Optional ByVal riskfactor As Double = 1) 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. '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

Beliebteste Forumthreads (12 Monate)

UDF Reihenfolge Übergabeparamter führt zu #WERT

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Links zu Excel-Beispielen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread