Annual Review Employee Rating Calculation

merivera

New Member
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!

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

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

Book1
ABCDEF
1RatingPayoutNameRatingPayout
200%Jon Jon2.5100%
31.10%Jane Doe3100%
41.40%John Doe10%
51.650%Sam Hill1.850%
61.975%Grant Luc0.80%
72.1100%Shelby Li2.9100%
82.4100%
92.6100%
102.9100%
Sheet10
Cell Formulas
RangeFormula
F2:F7F2=VLOOKUP(E2,\$A\$2:\$B\$10,2)

Replies
3
Views
166
Replies
5
Views
260
Replies
4
Views
455
Replies
8
Views
114
Replies
1
Views
2K

1,126,983
Messages
5,621,966
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back