Nested if

[shantraj.antin@gmail.com]

Dear Team,

I have time value in cell L2 in HH:MM formate. I am looking for If my call value is <=0:10 I need to display "< 10 min" M2 column, if value is between >0:10 and <0:15 M2 shoud diplay as "10-15 min" and value is between >0:15and <0:20 M2 shoud diplay as "15-20 min"

Similary i am looking for a range till <0:55 and <0:59 min

My function is becoming lengthy and not getting the accurate result
=IF(L6<="00:10","<10min",IF(AND(L6>="00:11",L6<="00:15"),"10-15 min","NA"))

Kindly help me on this problem

 

[Ravi Verma]

could you please share the xls file ?

 

[shantraj.antin@gmail.com]

hi Ravi

Ihave uploded the sample file for your reference.

 

[Abhijeet R. Joshi]

Use the text formula to capture the time duration, this should solve your problem half way.

Something like the below:

=IF(TEXT(L2,"HH:MM")<="00:10","<10 Mins","")

 

[shantraj.antin@gmail.com]

Hi Abhijeet,

Thanks for your response,

I am looking for all the ranges like 10-15 Min, 15-20 MIN, 20-25 Min, 25-30 Min, 30-40 Min.

Using nested if the formula should result the respective range. The above formula work only for "<10 Mins".

Regards,
Shan

 

[Abhijeet R. Joshi]

You can use the same looping pattern, just remember that if you are using <=10 so the next loop should be more than 10 and <=15 for the 10-15 mins bucket...

To make it more specific add the null values loop..
Have added a level more to help you out, you have to DIY the rest..

=IF($L2="","",IF(TEXT($L2,"HH:MM")<="00:10","<=10 Mins",IF(AND(TEXT($L2,"HH:MM")>"00:10",TEXT($L2,"HH:MM")<="00:15"),"10-15 mins","")))

 

[Luke M]

Since you already have a table started, I'd make range Q3:R9 look like this:
0:00 < 10 Min
0:10 10-15 Min
0:15 15-20 MIN
0:20 20-25 Min
0:25 25-30 Min
0:30 30-40 Min
0:40 > 40 Min

And then your formula is just:
=LOOKUP(L2,$Q$3:$R$9)