Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 Custom Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 Custom Function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 Custom Function
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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003: Having trouble with conditional formatting with custom user function (Repost) | Excel Worksheet Functions | |||
2003 - 2007 custom macro and custom button restore. | Excel Discussion (Misc queries) | |||
Excel 2003: Custom Views | Excel Discussion (Misc queries) | |||
Excel 2003: custom function with dotnet | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming |