Forumal Assistance

[Mpumiya]

please assist in creating formulas for the ratings. They are to convert the Scores into rating and color code them. I'm not sure if i'm clear.

EG: H18 should covert to 1 as a rating and color the cell red.

the rating is as follows: 0% - 84% = 1
85% - 89% = 2
90% = 3
91% - 95% = 4
96% - 100% = 5

[Glenn Kennedy]

Copy this from H18 to I22.

After row 22, I do not know what you expect to see.

=IF(H6="","",LOOKUP(H6,{0,0.85,0.9,0.91,0.96},{1,2,3,4,5}))

[Mpumiya]

currently I am plotting in the ratings manually, I want the sheet to populate H18 automatically and convert it to a rating (the 1 which is inserted)

[Glenn Kennedy]

I know that!! So did the formula work for rows 18 to 22, in your sheet (now attached here)??

It is what you want in Rows 23 and down that I do not understand your requirement.

[Mpumiya]

I am literally jumping up and down in the office, thanks a mil Glen you are awesome. This is exactly what I wanted and I have been struggling from Monday.

YOU ARE THE BEST

[Mpumiya]

Can you please advise on the color coding, is it feeding from my conditional formatting?

[Glenn Kennedy]

I didn't change anything regarding CF, as you didn't ask any specific questions...

[Mpumiya]

hi Glenn, It means that the CF is updating as I had it as part of the cell already. thank you so much. I have a lot of reports to start up in this department as it is new and I prefer to use excel as it makes life easier.

[Glenn Kennedy]

So... what more do you need me to do?

If you want something done, you need to explain WHAT you want and where you want it!!

[Mpumiya]

I'm working on one thing at a time, so I will ask you if I need something. Thanks Glenn you are heaven sent

[Mpumiya]

Please assist with the formula for adding a time rating formula.
please assist in creating formulas for the ratings. They are to convert the Scores into rating and color code them.
I'm not sure if I am clear.EG: C26 should covert to 1 as a rating AS FOLLOWS.

1= 1 min 30 secs and more
2= 1min 01secs - 1min 29 secs
3= 1min%
4= 59secs -31secs
5= 30secs secs and below

These ratings will be feeding of C17 which should be in time (Minutes and seconds).

[Glenn Kennedy]

This does not make sense.

1. Which sheet am I supposed to be looking at?

2. You say that C17 is a time. It isn't. It's a number on both sheets (6.00 on one sheet and 0.30 on the other). I do not understand..

[Mpumiya]

Morning Glenn, I need the cell to be in time. When I populate time it gave me the incorrect time as hours and I need it to be in minutes and seconds.

C17 Must equal 4hours in the Email Performance tracking sheet.
which means: C26 should covert to ratings as follows.
1= 6HRS and more
2= 4H01 to 5H59
3=4HRS
4= 2HRS to 3H59
5= 1H59 and less

C17 Must equal 1min on the Live chat tracking sheet.
EG: C26 should covert to ratings as follows.
1= 1 min 30 secs and more
2= 1min 01secs - 1min 29 secs
3= 1min%
4= 59secs -31secs
5= 30secs secs and below

I cant get the times right. I could only manage to populate a number and not the time as required

[Glenn Kennedy]

Format C17 as TIME on email performnce sheet, and enter data as 6:30 for 6 and a half hours.
Formula in C26 =

=IF(C17="","",LOOKUP(C17,{0,0.0833334,0.1666667,0.1673612,0.25},{1,2,3,4,5}))

[Glenn Kennedy]

Having second thoughts about this. will you be doing ANYTHING with these times AS WELL as calculating the performance criteria???

[Mpumiya]

We have to measure how long they take to respond to a query from the second it comes in. this is in 2 different departments.

For emails we calculate in hours, they have up to 4 hours to respond.

For Live chats they have to answer within 1 minute.

[Mpumiya]

The email formula you sent is perfect and now the Live chat one is giving me grey hairs

[Glenn Kennedy]

You miss the point of my Q. will you be doing anything ELSE with these times?

[Glenn Kennedy]

See how you like this.

Enter times <1 minute as a 2 digit number;
45 seconds as 45

Enter times >= 1 minute as a 3 digit number

1 minute as 100
2 minutes 30 seconds as 230,

Cell C17 formatted as 0\:00

This formula in C26. If you like this, the frst one can be adjusted to match....


=IF(C17="","",LOOKUP(C17,{0,31,100,101,130},{5,4,3,2,1}))

[Mpumiya]

Thank you very much Glenn, this has been an educational journey for me. God Bless

[Richard Buttrey]

@Mpumiya
Somewhat late in the day since I've only just seen this but please note our 1st Forum rule about thread titles and make sure next time that you create something meaningful.