Need to allocate funds based on different criteria

[queenstarks]

I am having a big dilemma right now trying to figure out how to make my calculations work or even where to begin. I have a fixed amount of money that I need to distribute to employees based upon a percentage of salary they receive for performance. For instance, employee A is outstanding therefore, she gets 3%. employee B is rated good and gets 2%. If I have 30 employees receiving varying percentages but only have a total of $100,000 to spend, how can I get it to allocate the funds?

 

[Joe4]

Welcome to the Board!

Do your percentages add up to 100%?
If so, then you would just take each person's percentage and multiply by your total (i.e. =3% * 100000).

If your percentages do not add up to 100%, then I am not sure that they have much meaning. If that is the case, perhaps you want to add up all of the percentages to get a total, and then take each person's portion of the percentage and multiply by your total.

For example, let's say that the percentages are in column A, form A2:A11. To figure out how much the person in row 2 would get, your formula would look something like this:
=100000 * (A2/SUM($A$2:$A$10))

 

[queenstarks]

Thanks for your response