Annual Review Employee Rating Calculation

merivera

New Member
Joined
Feb 7, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,739
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top