# Agent Rating Calculation

#### Lasantha

##### Member
Dear All,

Kindly look into the attachment. Could you please help me to get ratings to "Column C" , according to given criteria. thank you.

Actual shift time is total working hour per week.

Thank you
Lasantha.

#### Attachments

• 9 KB Views: 8

#### bosco_yip

##### Excel Ninja
In C2, copied down :

=LOOKUP(B2*24,0+LEFT(A\$12:A\$25,FIND("-",A\$12:A\$25)-1),B\$12:B\$25)

Regards
Bosco

#### AlanSidman

##### Active Member
1. For this to work, you need to have your staffed time and staff hours be the same type of data. I suggest that you convert all to decimal hours and not hours:minutes:seconds and then multiply it by 24 so that it represents true hours.
2. Your look up table needs to numbers and not a range of numbers. That table should look like the following
Data Range
 A​ B​ 10​ Criteria​ ​ 11​ Staffed Hours​ Rating​ 12​ 0​ 0​ 13​ 4​ 3​ 14​ 8​ 3.5​ 15​ 12​ 4​ 16​ 16​ 4.5​ 17​ 20​ 5​ 18​ 24​ 5.5​ 19​ 28​ 6​ 20​ 32​ 6.5​ 21​ 36​ 7​ 22​ 40​ 7.5​ 23​ 46​ 8​ 24​ 48​ 8.5​ 25​ 50​ 9​

You can then employ a Vlookup function in C2 and copy it down. =VLOOKUP(B2,\$A\$12:\$B\$25,2,TRUE)

#### Attachments

• 9.7 KB Views: 3

#### Lasantha

##### Member
In C2, copied down :

=LOOKUP(B2*24,0+LEFT(A\$12:A\$25,FIND("-",A\$12:A\$25)-1),B\$12:B\$25)

Regards
Bosco
Thank you very much.

#### Lasantha

##### Member
1. For this to work, you need to have your staffed time and staff hours be the same type of data. I suggest that you convert all to decimal hours and not hours:minutes:seconds and then multiply it by 24 so that it represents true hours.
2. Your look up table needs to numbers and not a range of numbers. That table should look like the following
Data Range
 A​ B​ 10​ Criteria​ ​ 11​ Staffed Hours​ Rating​ 12​ 0​ 0​ 13​ 4​ 3​ 14​ 8​ 3.5​ 15​ 12​ 4​ 16​ 16​ 4.5​ 17​ 20​ 5​ 18​ 24​ 5.5​ 19​ 28​ 6​ 20​ 32​ 6.5​ 21​ 36​ 7​ 22​ 40​ 7.5​ 23​ 46​ 8​ 24​ 48​ 8.5​ 25​ 50​ 9​

You can then employ a Vlookup function in C2 and copy it down. =VLOOKUP(B2,\$A\$12:\$B\$25,2,TRUE)
thank you Very much.