Good morning,
I am using the below formula to determine a score based on a range of results. The Source cell is currently formatted as [h]:mm:ss. In the example, Cell H33 has 0:11:49 and Cell H34 (results) has a score of 5 which isn't inline with the formula.
How can I get the below formula to recognize the data in cell H33 and return the correct result?
Formula:
Please Login or Register to view this content.
Thanks
A result of 5 looks perfectly in line with the logic of the formula. Can you explain why it is not?
My guess is it is related to not understanding how Excel stores dates/times (review this: http://www.cpearson.com/Excel/datetime.htm ). If H33 contains 0:11:49 (my interpretation is 11 minutes 49 seconds), this is stored in Excel as the number 0.00821, which is much less than 449.9, so Excel returns 5. A cell value of 450 would correspond to about 10800 hours, so I would not expect a result other than 5 until your input cell is greater than 10800 hours.
I am not sure how to read your formula, so I cannot recommend a specific solution. I would make two recommendations. 1) Study how Excel stores date/time values so you know exactly what values to put into your comparison formula. 2) Unrequested, but I would probably make this a lookup table lookup function -- especially if the number of conditions can become larger or the cutoff points may change. In my opinion, a lookup table/lookup function approach will be more flexible -- if flexibility is needed.
Originally Posted by shg
Basically what I am calculating is end of year performance results for a call center. This is an average handle time metric and the ranges are in seconds. a 5 rating would be 449.9 seconds or below. A 4 rating is 450 - 509.9 seconds. A 3 rating is 510 - 569.9 seconds, A 2 rating is 570 - 630 seconds and a 1 rating is 630 seconds and above.
The result for the agent is 709 seconds which I attempted to convert to minutes and seconds. At 709 seconds the rating should be a 1.
5 rating = 449.9 and under
4 rating = 450 – 509.9 sec’s
3 rating = 510 - 569.9 sec’s
2 rating = 570 – 630 sec’s
1 rating = > 630 sec’s
Your idea of using a lookup table may be the best solution and I haven't thought of that. (New to excel formulas)
As explained in the link, Excels stores time values as fractions of a day, So 450 seconds is (450/3600/24) 0.0052083333 of a day. Setting this up as a lookup table:
Then, using the "approximate match" option =VLOOKUP(H33,$B$2:$C$6,3,TRUE). Remember that the time time value in the lookup function must be a time serial number (number format should not matter) as long as the time value is an actual time value (assuming times between 0 and 1000 seconds are typical, the actual values in the lookup should be between 0 and 0.0116).
Please Login or Register to view this content.
If you decide to stick with the IF() function, you just need to convert your decimal seconds values into a time serial number using any of the suggested functions in column B above. =IF(...(IF(H33<T I M E (0,0,450),5,IF(...)) [spaces added because the site's firewall doesn't like me today]
Thank you for the response. This is much more advanced than I currently understand but I will review the link you sent and increase my knowledge in this area.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks