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

[ben]

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" wrote:

> 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
>

[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
ben <ben@discussions.microsoft.com> wrote in message news:<0A4CBD3A-A2A1-4A0A-B066-8614B617F79C@microsoft.com>...
> 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" wrote:
>
> > 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
> >