Felix Atagong
08-25-2005, 04:20 AM
(this item has also been posted on MrExcel.com, but no answers there)
I have a UDF that calculates a certain percentage (= profit) on top of the given amount, but in between a minimum and maximum (Excel2000).
When using the Formula button the so-called Formula Palette opens and people can fill in the amounts in the right boxes. But on built-in functions partial results show when entering a box, but this doesn't happen in my UDF.
Example (from what I would like to see to appear in the formula palette):
Amount: 100 = 100
Minimum: 10 = 110 This just shows 10, not 110.
Percentage: 50 = 150 This shows 50, not 150.
Maximum: 30 = 130 This shows 30 not 130.
The final result is 130 (this does happen in my UDF).
Any suggestions?
Public Function MinPerMax( _
Amount As Double, _
Optional Minimum As Double, _
Optional Percentage As Double, _
Optional Maximum As Double) _
As Variant
If IsMissing(Amount) Or Amount = Empty Then
MinPerMax = 0
Else
Dim Resultaat(1 To 3) As Double
If IsMissing(Minimum) Or Minimum = Empty Then
Minimum = 0
End If
Resultaat(1) = Amount + Minimum
If IsMissing(Percentage) Or Percentage = Empty Then
Percentage = 0
End If
Resultaat(2) = Amount * (100 + Percentage) / 100
If IsMissing(Maximum) Or Maximum = Empty Then
Maximum = 9999999
End If
Resultaat(3) = Amount + Maximum
If Resultaat(1) > Resultaat(2) Then
MinPerMax = Resultaat(1)
Else
MinPerMax = Resultaat(2)
End If
If MinPerMax > Resultaat(3) Then
MinPerMax = Resultaat(3)
End If
End If
If Minimum > Maximum Then
MinPerMax = "ERROR: max < min"
End If
End Function
I have a UDF that calculates a certain percentage (= profit) on top of the given amount, but in between a minimum and maximum (Excel2000).
When using the Formula button the so-called Formula Palette opens and people can fill in the amounts in the right boxes. But on built-in functions partial results show when entering a box, but this doesn't happen in my UDF.
Example (from what I would like to see to appear in the formula palette):
Amount: 100 = 100
Minimum: 10 = 110 This just shows 10, not 110.
Percentage: 50 = 150 This shows 50, not 150.
Maximum: 30 = 130 This shows 30 not 130.
The final result is 130 (this does happen in my UDF).
Any suggestions?
Public Function MinPerMax( _
Amount As Double, _
Optional Minimum As Double, _
Optional Percentage As Double, _
Optional Maximum As Double) _
As Variant
If IsMissing(Amount) Or Amount = Empty Then
MinPerMax = 0
Else
Dim Resultaat(1 To 3) As Double
If IsMissing(Minimum) Or Minimum = Empty Then
Minimum = 0
End If
Resultaat(1) = Amount + Minimum
If IsMissing(Percentage) Or Percentage = Empty Then
Percentage = 0
End If
Resultaat(2) = Amount * (100 + Percentage) / 100
If IsMissing(Maximum) Or Maximum = Empty Then
Maximum = 9999999
End If
Resultaat(3) = Amount + Maximum
If Resultaat(1) > Resultaat(2) Then
MinPerMax = Resultaat(1)
Else
MinPerMax = Resultaat(2)
End If
If MinPerMax > Resultaat(3) Then
MinPerMax = Resultaat(3)
End If
End If
If Minimum > Maximum Then
MinPerMax = "ERROR: max < min"
End If
End Function