Decreasing Percent amount based on Ranking

[DmaxHunter]

Hello,

I'm trying to build a potential bonus structure where we rack and stack (rank) each employee and based off their ranking they get a % of the bonus we have available for distribution for the year. #1 gets the highest % of the bonus and it incrementally decreases from there, all totaling 100%.

I can figure this out manually by simply playing with numbers and coming up with what the decrease is for each decreased rank. In my example I have 18 employees so I found #1 gets 7.8605% then #2 gets .0025 off of it then #3 .0025 off it and so on. But if I add say a 19th employee I have to play around with the numbers to find my starting % for #1 and my decreasing % for each. So, is there a formula I can implement to do this automatically so when I add the 19th employee it re-calculates all on it's own simply based on the value in D2 and I don't have to just sit there and play with the numbers to try and figure it out?


Here are some visuals for what I have so far. Thanks in advance for any help!

 

[steve the fish]

Try this out:

=RANK(B5,$B$5:INDEX($B$5:$B$1000,$D$2),0)/SUM($B$5:INDEX($B$5:$B$1000,$D$2))

 

[DmaxHunter]

Yep, that does it perfectly! Thank you so much! I never would have figured that one out.

 

[DmaxHunter]

Okay while that works great, I want to throw a curveball at you. Say I don’t want to pay anyone with the company less than 6 months (in gray #’s 13-18) more than $275 (or another set cap as seen in N10). Is there any way to add that into the formula and distribute $275 and progressively less for the last 6 employees (13-18) and remove that sum from the total of $20,000 (N9) which that remainder would get distributed to the everyone else who has been there more than 6 months (1-12).

Not sure if that makes sense. Here are two more screenshots showing my progress so far using your solution as a nested IF, but I just use a .0025 off that 13th position again. And of course doing so doesn’t equate to 100% total the way I’m doing it.

 

[DmaxHunter]

Curious if anyone has any ideas for this? I've been creating a lot of helper columns but still don't seem to be getting close enough. Thinking it might just be easiest to split these into two different sections for now and using steve the fish's solution in two separate sections

 

[KRODRIGUEZ2021]

Would you mind sharing this spreadsheet? This is exactly what I am needing but I'm excel illiterate.