Calculation with variables

Dear all, I have a problem calculating some bonuses and I would like to ask for your support. I will try to describe the case as much as detailed as possible: In have an office in which a total of 5 employees work. In our company we usually give a year bonuses to our employees. The bonus system work as follows: at the end of the year a certain amount (just for the example lets say 5 000 USD) has to be distributed between all employees. The bonus that each employee will get is based on two parameters with equal weight - fulfillment of sales targets and client satisfaction. So I have to score every employee based on those two parameters (and the scoring will be from 1 to 5, 5 being the highest score) and then based on the final score - to allocate to each employee the needed bonus. This is where I stuck. The way I imagine it the best employee can get a score of 10 and the worse - a score of 2. But they are also a lot of possibilities in between. The system works like this
1) If an employee has a score 10 - then he should get 30 % of the total bonus
2) If an employee gets a score 8,9 - then he should get 25 % of the total bonus
3) If an employee gets a score 6,7 - then he should get 20 % of the total bonus
4) If an employee gets a score 4,5 - then he should get 15 % of the total bonus
5) If an employee gets a score 2,3 - then he should get 10 % of the total bonus.

the problem is that according to the system I can score only one person with 10 and only one person with 2, but at the same time give or 3 or 4 or 5 or 6 or 7 or 8 or 9 to all the rest at the same time (meaning that each one of the other employees have 7 for example). It gets even more terrible, because there is a possibility also not to give both 10 and 2 and two of them might have 8 (this is already 50 %) and the other 3 employees can have 6 (so 3*6 = 3*20 % = 60 %). As a result I am short with 10 % (because I only have 100 % - 5 000 USD). Is there any possibility to make excel calculate the bonus each of the employees has to receive based on the score he has and if needed (in case that the bonus pool is exceeded somehow) to recalculate the amount each employee has to receive (following the logic that if both employees have the same score, they have to get the same % of bonus from the total bonus)

I now is sounds very complicated , but this is a task I have to figure out how to deal with and I will be very grateful if somebody of you can help me with this.

Thank you very much in advance

Hello,

Maybe this will work for you?


Simeon

Great! I think that it will work perfectly. Thank you very much