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 |
Thank you very much.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.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)