Excel 2003 Custom Function

[Heather]

Hi I am trying to create a custom function to calculate bonuses for
asset managers. I am having problems getting excel to calculate when
the percentages are equal. The code works when using > or < but not

= or <=. I have also tried using the ROUND function to round the

percentages off. Any ideas?

Function PerformanceOneYear(ACM_Perform As Single, BESA1yr As Single,
Bases_Points As Single, _
Limit As Single, GrowthValue1yr As Currency, Max_Bonus As Currency)
'Calculates the Performance bonus for 1 year period
'ACM_Perfom is the Asset Managaer's growth perfomance for the year as
a percentage
'BESA1yr is the BESA index for the year as a percentage
'Bases_Points is the percentage above the BESA index required for a
Bonus
'Max_Bonus is the maximum currency value of the bonus payable
'Limit is the maximum percentage above BESA index payable for bonus

If ACM_Perform >= (BESA1yr + Bases_Points) Then
If (ACM_Perform - (BESA1yr + Bases_Points)) <= Limit Then
If ((ACM_Perform - (BESA1yr + Bases_Points)) *
GrowthValue1yr) < Max_Bonus Then
PerformanceOneYear = (ACM_Perform - (BESA1yr +
Bases_Points)) * GrowthValue1yr
Else
PerformanceOneYear = Max_Bonus
End If
Else
If ((BESA1yr + Limit) - (BESA1yr + Bases_Points)) *
GrowthValue1yr < MaxBonus Then
PerformanceOneYear = ((BESA1yr + Limit) - (BESA1yr +
Bases_Points)) * GrowthValue1yr
Else
PerformanceOneYear = Max_Bonus
End If
PerformanceOneYear = "No Performance Bonus"
End If
Else
PerformanceOneYear = "No Performance Bonus"
End If

End Function

Thanks
Heather

 

[Guest]

the simple but probably not most efficient solution,
try an 'or' statement
instead of if a => b then
use
if a = b or a > b then
i know seems repetitive but sometimes that works.

 

[Heather]

Hi Ben
Thanks for the suggestion, but I seem to think the problem is that
Excel uses up to 15 decimal places and although when testing the
function I may input a percentage as 6%, when the calculations are
done, Excel uses 0.06??????? and so if I then input a further 2% and
4%, when I add these together, they will never exactly equal the 6%.
Has any one else got a suggestion?
Thanks
Heather