Probably an easy solution

[livermorongeorge]

I've never really used anything except basic calculations in excel, so it's likely that this is a relatively simple maneuver, but I've been unable to figure it out on my own. Hoping you guys can help.


This is for a scoring system in regards to a certification and raise promotion system at my work (I manage a coffee shop.) I've already set up the sheet to add up totals for the test and then combine to find the overall score in the form of percentage. (0% - 100%)

What I WANT to do is use this percent to calculate the employee's raise based on range inputted (so, if they have a max pay of 10 bucks/hr, and are currently making 8 bucks/hr, they'll get a 2 dollar raise with a 100% score.)

My problem lies with the percentage system itself. I do NOT want employees scoring lower then 60% to earn ANY raises, so I'm trying to set up a system where a 60% test score equals a 0% score on the raise calculation scale, and a 100% score on the test equals a 100% score on the raise scale.

I'm guessing there has to be a way to do this, given the complexities of some of these formulas, but I'm at a loss... Hopefully this makes sense to ya'll. I was never good at math.

Free pound of coffee to the first one to help me!

-George

Removed e-mail address - You can use the secure PM feature instead of putting your address in a public forum - Moderator

 

[lenze]

Hi and welcome to the board!!!
I'm not clear on how you figure the raises!! In your example, the employee making 8.00/hr gets a raise to the max(10.00) of 2.00 if they score 100%. What if they score 80%?,90%? What if an employee is making 7.50/hr?, 8.50/hr?

lenze

 

[livermorongeorge]

Hi and welcome to the board!!!
I'm not clear on how you figure the raises!! In your example, the employee making 8.00/hr gets a raise to the max(10.00) of 2.00 if they score 100%. What if they score 80%?,90%? What if an employee is making 7.50/hr?, 8.50/hr?

lenze

The sheet allows for inputting of the current pay rate and the maximum pay rate (so that we can change things easily if an employee has already received a raise, or minimum wage goes up.) The current rate is subtracted from the maximum to receive the pay rate range (so, a $10 max pay rate with a current rate of $8.75 returns a pay range of $1.25.)

It is THIS range, the $1.25, that I want the score to affect, using a percentage range based on the passing score of the test. (again, I only want to award raises based on scores from 60% to 100%, my hope is that I can use functions to turn this 40% difference into a 100% range of some sort)

So, if an employee scores 80% on the test, and has a pay rate RANGE of $3.00, they wouldn't receive an 80% raise of $2.40, but would instead receive a 50% raise (given that 80% is midway between 60% and 100%) of $1.50.

Hope that makes sense. Math has never been my strong suit, but I want to be able to reward my staff with something. They've suffered pay freezes through this economic crap, and I feel they really deserve a program that's going to reward them fairly.

-George

 

[lenze]

A1 = Test Score
A2 = Max Rate
A3 = Current Rate

=IF(A1<0.6,0,(A1-0.6)/0.4*(A2-A3))

HTH
lenze

 

[livermorongeorge]

A1 = Test Score
A2 = Max Rate
A3 = Current Rate

=IF(A1<0.6,0,(A1-0.6)/0.4*(A2-A3))

HTH
lenze

You, sir, are my savior. I'm serious about that coffee offer, btw. You PM me someplace to send it, and I'll make it happen :D

-George

 

[lenze]

Not a coffe drinker. If you want, send it to Bill(AKA MrExcel), my compliments
MrExcel.com
13386 Judy Ave. NW
P.O. Box 82
Uniontown, Ohio 44685

lenze