• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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.
 
Top