Hi all!
I'm creating a sliding scale for my customer service team's bonuses and I need some help determining a simple formula. Here's my example:
The top bonus is 2.5% (.025)
The bottom bonus is 0.5% (0.005)
The top performer completed 931 orders, so he'll get the full 2.5%. The bottom performer completed 641 orders, so he'll get 0.5%. How do I figure out the bonus percentages for everyone in between? Here's my data set:
931
867
844
824
817
810
768
751
719
717
690
677
641
Thanks!
You intervals should be determined as below
=(Highest bonus - Lowest bonus)/(COUNT of performers-1)
Hence, where cells A1 to A13 hold your values use
=(0.025-0.005)/COUNT(A$1:A$13)-1
Now subtract the resultant value from 2.5% for the next bonus value and so on..
Last edited by Ace_XL; 01-02-2015 at 04:51 PM.
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks Ace. I don't think I'm doing this right... here's what I'm getting:
931 -1.00
867 -1.00
844 -1.00
824 -1.00
817 -1.00
810 -1.00
768 -1.00
751 -1.00
719 -1.00
717 -1.00
690 -0.99
677 -0.99
641 -0.98
I read your question a little differently:
Row\Col B6: =(A6-A$4) / (A$2 - A$4) * (B$2 - B$4) + B$4
Ace's interpretation may be correct though.
Entia non sunt multiplicanda sine necessitate
Perfect, thanks SHG!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks