Employee Bonuses

**dengar169** · 12-23-2010, 06:47 PM

Hi. Brand new user.

I have a list of employees production values (In row A). From this, how do I equate the highest value to be 100% and each value below to be a percentage OF this 100%?

I will be using this monthly where the employee with 100% would get $500 each month, for example, and each employee below would get a percentage of $500. Each month the highest value would be different based on how fast they worked

Thanks so much!!!

**JBeaucaire** · 12-23-2010, 06:56 PM

Assuming the list of values is in column A starting at A2 and going down, put this formula in B2, then copy down:

=A2/MAX(A:A)

Be sure to format column B as percentage.

**JBeaucaire** · 12-23-2010, 07:00 PM

To get the $ value assigned to each employee, put this in C2, then copy down...format this column as $:

=IF(A2=MAX(A:A),500,500*A2/SUMIF(A:A,"<" & MAX(A:A),A:A ))

**dengar169** · 12-23-2010, 07:08 PM

Wow! Thanks so much JBeaucaire. One thing though, the formula for the $ Value came out off. Let's say a2 is 233 (of item per hour), which is 53.56% from the highest producer. It gave me a dollar amount of $215,740.74. I'd rather not pay that much in bonus. haha

**martindwilson** · 12-23-2010, 07:11 PM

have you a max bonus?
say its 1000
then wrap it up in MIN
=min(formula to return bonus,1000) then it will never exceed 1000

**dengar169** · 12-23-2010, 07:13 PM

Originally Posted by martindwilson

have you a max bonus?

Yes. Sorry if I explained poorly. The max bonus in this case would $500. each person's would less of $500 based on their production percentage. So, if they made 100%, they would all get $500.
(hopefully that makes sense)

**martindwilson** · 12-23-2010, 07:19 PM

id don't get that problem with jb's solution 233 in a2 gives

$500.00
$79.55
$68.18
$34.09
$22.73
$34.09
$56.82
$102.27
$39.77
$62.50

**dengar169** · 12-23-2010, 07:24 PM

Originally Posted by martindwilson

id don't get that problem with jb's solution 233 in a2 gives

$500.00
$79.55
$68.18
$34.09
$22.73
$34.09
$56.82
$102.27
$39.77
$62.50

I must have copied the formula wrong. I retried and it works. However the dollar amount doesn't seem right. here is an example of data:
435 100.00% $500.00
335 77.01% $129.44
248 57.01% $95.83

77% of 500 would $385. Sorry but I'm a Excel novice so I'm not sure where in the formula that would be

**martindwilson** · 12-23-2010, 07:47 PM

maybe you just need in c2 down
=IF(A2=MAX(A:A),500,500*B2)
that would give $ amounts as % of 500

**JBeaucaire** · 12-24-2010, 03:04 PM

Percentage of $500 is wrong, IMO. The remaining people need to pool their sales, then see what THEIR percentage is of the pool of sales, then they get that percentage of $500. That's what my original formulas do.

I posted a sample file. Feel free to edit it to have actual values and manually mockup your expected results so we can see how your math is supposed to work right on a ws.