Agent Rating Calculation

[Lasantha]

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.

 

[bosco_yip]

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]

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)

 

[Lasantha]

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]

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.