IF statement - why can't I see the issue??

[Mel_Dyson]

Hi Guys, this should be an easy one, yet I am so stuck.

I'm trying to write an IF statement that will return categories, 30 minutes or under, between 31 and 60 minutes, between 61 and 90 minutes, between 91 and 120 minutes and over 120 minutes.

I can't workout what I am doing wrong??

=IF(E2<31,"less than 30 minutes",IF(E2>=31<=60,"Between 31 and 60",IF(E2>=61<=90,"Between 61 and 90",IF(E2>=91<=120,"Between 91 and 120","Greater than 120"))))

Your advice is greatly appreciated :) 

[#2]

@Mel_Dyson 

A condition such as E2>=31<=60 is not valid in Excel. Do it like this:

=IF(E2<=30,"30 minutes or less",IF(E2<=60,"Between 31 and 60",IF(E2<=90,"Between 61 and 90",IF(E2<=120,"Between 91 and 120","Greater than 120"))))

[#3]

@Mel_Dyson 

I didn't try to diagnose what you were doing wrong. I'll just say in general that nesting a lot of IF functions always gets tricky. It's probably why Excel now offers an IFS function.

This one works for you
=IFS(E2<31,"less than 30 minutes",E2<=60,"Between 31 and 60",E2<=90,"Between 61 and 90",E2<=120,"Between 91 and 120",E2>120,"Greater than 120")

What it does is to stop calculating once a condition is met. The general syntax is

IFS(condition1, result1,condition2,result2,condition3,result3.......)

[#4]

@Mel_Dyson 

As variant

=LOOKUP(E2,
  {0,31,61,91,121},
  {"less than 30 minutes","Between 31 and 60","Between 61 and 90","Between 91 and 120","Greater than 120"}
)

[#5]

oh to think that is what it was! Thank you so very much I really appreciate it! Have a wonderful week!

[#6]

Thank you so very much - I get it, great explanation. Have a great week!

[#7]

Excellent value add thank you Sergei! I've noted that option down for future reference. Have a great week!