Morning all,
I need to calculate my bonus pay-out against achievements as follows:
- Above 95% achievement but below or equal to 100% the pay-out follows a linear rule.
- Above 100% and until 110% the pay-out doubles thus reaching 200% at 110%.
-
I am using this formula but not sure is right:
=IF(I16<0,95;0;IF(I16<=1;(I16-0,9)/0,1;IF(I16>1,2;2;1+((I16-1)/0,2))))*H16*$M$5
I6 = achievement %
H16= input data i.e. sales
M5 = salary info
Thanks all in advance,
Hi and welcome to the forum.
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.
Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
It's also slightly confusing since you refer to cell I16. I presume that's a typo and you mean I6 but no doubt sight of your actual workbook with worked examples will clear this up.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi JJJMad
I suspect that your bonus pay-out depends only on the achievement% in cell [I16] , rather that the [H16] Actual Sales.
If a person has a Sales Target of X, and achieves actual Sales of Y, then their achievement% is (Y/X)%
If a person reaches less than 95% of their Sales Target, they get no salary bonus.
If the person reaches 95% of their Sales Target, they will get a bonus of 50% (looking at your formula)
If the person reaches 96% of their Sales Target, they will get a bonus of 60%
If the person reaches 97% of their Sales Target, they will get a bonus of 70%
If the person reaches 98.2% of their Sales Target, they will get a bonus of 82%
If the person reaches 99.4% of their Sales Target, they will get a bonus of 94%
If the person reaches 100% of their Sales Target, they will get a bonus of 100%
If the person reaches 105% of their Sales Target, they will get a bonus of 125%
When a person reaches, or exceeds, 110% of their Sales Target, they will get a maximum bonus of 200%
So I suggest your formula for the bonus% should be..
=IF(I16<0,95;0;IF(I16<=1;(I16-0,9)/0,1;IF(I16>=1,1;2;1+((I16-1)/0,2))))
or, for bonus amount based on salary
bonus amount=IF(I16<0,95;0;IF(I16<=1;(I16-0,9)/0,1;IF(I16>=1,1;2;1+((I16-1)/0,2))))*$M$5
zeddy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks