Multiple IF AND Statements

[jlenn10]

I currently manage my team's productivity tracker and am trying to do Multiple IF AND statements to categorize cases that have been open for 30,60,90,120 days. Cell AI5 is the number of days a case is open for. Here's the formula that I've written, part of it works but the rest does not. what am I doing wrong? I would include the attachment but it's a google sheet and loaded with PHI.

=IF(AG4=0,"",IF(AG4=FALSE,"",IF(AI4<29,"Open Under 30 days",IF(AND(AI4>=30,AI4<60,"Open Over 30 Days","",IF(and(AI4>=60,AI4<90,"Open Over 60 Days","",IF(and(AI4>=90,AI4<120,0,"Open Over 90 Days","",IF(and(AI4>=120,AI4<365,0,"Open Over 120 Days","")))))))))))

Thanks for your help!
James

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(OR(AG4=0,AG4=FALSE),"",IF(AI4<29,"Open Under 30 days",IF(AI4<60,"Open Over 30 Days",IF(AI4<90,"Open Over 60 Days",IF(AI4<120,"Open Over 90 Days",IF(AI4<365,"Open Over 120 Days",""))))))

Please note, as this is a Goggle Sheets questions, it should be in the "General Discussion & Other Applications" section, I have moved it for you this time.

 

[Joe4]

it's a google sheet

Please be sure to post all Google Sheet questions in the "General Discussion & Other Applications" forum (note the description there), and always be sure to mention that it is a Google sheet question, as they are not the same as Excel.

I have moved this thread for you.

 

[p_s]

I currently manage my team's productivity tracker and am trying to do Multiple IF AND statements to categorize cases that have been open for 30,60,90,120 days. Cell AI5 is the number of days a case is open for. Here's the formula that I've written, part of it works but the rest does not. what am I doing wrong? I would include the attachment but it's a google sheet and loaded with PHI.

=IF(AG4=0,"",IF(AG4=FALSE,"",IF(AI4<29,"Open Under 30 days",IF(AND(AI4>=30,AI4<60,"Open Over 30 Days","",IF(and(AI4>=60,AI4<90,"Open Over 60 Days","",IF(and(AI4>=90,AI4<120,0,"Open Over 90 Days","",IF(and(AI4>=120,AI4<365,0,"Open Over 120 Days","")))))))))))

Thanks for your help!
James

In your original function, your AND statements are not closed. Which is why it is giving you an error.

Original Formula (last section of formula using and statement):

IF(and(AI4>=120,AI4<365,0,"Open Over 120 Days",""))

The syntax would be

IF(AND(criteria1, criteria 2),Cell Value if TRUE, Cell Value if False)

So if we look at your formula, for the syntax to be correct, it should be:

IF(AND(AI4>=120, AI4<365),"Open Over 120 Days","Open Over 1 Year")

But the code provided by Fluff is definitely cleaner and more beautiful. I was just explaining why you were getting an error in your formula. The only change I'd make is: IF(AI4<30,"Open Under 30 days",....), Because if you say <29, it will not consider 29 as well. So you could either do <=29, or <30.


Additionally your previous IF statements have too many variables. If statements can only have 3 variables, i.e.,

1. a condition/criteria that must be met,
2. the task to be executed if TRUE,
3. the task to be executed if FALSE.

Let's break your code down:

1st IF statement: Syntax is OK:

1. Criteria to be met: IF(AG4=0,
2. If TRUE: "",
3. If FALSE: IF(AG4=FALSE,"",IF(AI4<29,"Open Under 30 days",IF(AND(AI4>=30,AI4<60,"Open Over 30 Days","",IF(and(AI4>=60,AI4<90,"Open Over 60 Days","",IF(and(AI4>=90,AI4<120,0,"Open Over 90 Days","",IF(and(AI4>=120,AI4<365,0,"Open Over 120 Days","")))))))))))

2nd IF Statement: Syntax is OK

3rd IF Statement: Syntax is OK

4th IF Statement: Assuming your AND Statement was correctly written as per above:

1. Criteria: IF(AND(AI4>=30,AI4<60),
2. IF TRUE: "Open Over 30 Days",
3. IF FALSE: "",
4. Additional variable which is causing a syntax error: IF(and(AI4>=60,AI4<90),"Open Over 60 Days","",IF(and(AI4>=90,AI4<120),0,"Open Over 90 Days","",IF(and(AI4>=120,AI4<365),0,"Open Over 120 Days","")))))))))))

In this case, 4, would take the place of 3.

This error repeats it self in all the following IF statement, except the last one, but in the last one you've switched the IF TRUE and IF FALSE statements.

 

[p_s]

Also, James, Google Sheets will tell you what your errors are usually, but perhaps it wasn't because your entire If statement was within an AND criteria and didn't have any executions? IDK. It probably gave you a "too few arguments/criteria/variables" error.

 

[jlenn10]

Thank you everyone! The feedback and solutions are incredible. I will post in the correct forum in the future, looking forward to using this more often.

 

[Fluff]

Glad we could help & thanks for the feedback.