Annual Review Employee Rating Calculation

[merivera]

Hi-

I need help trying to come up with a formula that will retrieve a set payout percent based on a predetermined employee rating. Please see my table data below.

Rating Payout
2.9-3 100%
2.6-2.8 100%
2.4-2.5 100%
2.1-2.3 100%
1.9-2 75%
1.6-1.8 50%
1.4-1.5 0%
1.1-1.3 0%
0-1 0%


The employee rating is predetermined. Using the list of test employees below. What would my formula look like

EE Name Individual Rating Individual Results (Payout %)
Jon Jon 2.5
Jane Doe 3
John Doe 1
Sam Hill 1.8
Grant Luc .80
Shelby Li 2.9

My table would be housed on the first tab in excel, the employee data & where I need to plug in my formula ( Individual Results (Payout %)) would be housed on the second tab.

For example Sam Hill who received a 1.8 rating would be at 50%

Help I need to solve this asap.

Thank you!

 

[Eric W]

If you rearrange your table slightly, you can do this:

Book1
	A	B	C	D	E	F
1	Rating	Payout		Name	Rating	Payout
2	0	0%		Jon Jon	2.5	100%
3	1.1	0%		Jane Doe	3	100%
4	1.4	0%		John Doe	1	0%
5	1.6	50%		Sam Hill	1.8	50%
6	1.9	75%		Grant Luc	0.8	0%
7	2.1	100%		Shelby Li	2.9	100%
8	2.4	100%
9	2.6	100%
10	2.9	100%
Sheet10

Cell Formulas
Range	Formula
F2:F7	F2	=VLOOKUP(E2,$A$2:$B$10,2)