Mar 15 2021 07:22 AM
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 :)
Mar 15 2021 08:07 AM
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"))))
Mar 15 2021 08:11 AM
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.......)
Mar 15 2021 09:03 AM
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"}
)
Mar 15 2021 05:27 PM
Mar 15 2021 05:29 PM
Mar 15 2021 05:31 PM