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

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"))))

6 Replies

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

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"))))

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

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.......)

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

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"}
)``````

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

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

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

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

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

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