IF(AND) can't seem to sort out the order
I've read, reread and reread again a number of posts/articles about IF(AND) expressions but for some reason it's just not getting through my thick head and I think my mysql expressions aren't letting me mentally work this out.
This is the specific expression I've been trying to sort out - this is unparsable
=IF(AND(Department2 < >"Ship", "ERROR, "") [End Date]6 < [End Date]7), "ERROR", "")
What I'm trying to do is have the worksheet flag any changes I've made in a schedule in calendar view that violate the date range ie. I have 5 sequential steps but I don't want to use dependencies for reasons too detailed to explain here.
I have it arranged in "backwards" order ie. last step above next last or a,b,c,d,e - step a = ship and then it goes to the next order.
So row 7 = last step in the process or "Ship", row 6 would be the first step in a new order or "Receive" and so on.
1) Logic - If the End Date for step a) is sooner than step b) display the word "Error", if not then blank
2) Logic - If the Department is not equal to "Ship" and condition 1 is true then display "Error", otherwise display a blank.
The reason for 2) is that it will always be < because it's reading a new order so rather than get warned about false positives I need the second condition.
Any assistance with this would be greatly appreciated.
Carl
Comments
-
Let me see if I can break this down for you.
=IF(AND(statement 1, statement 2, statement 3), then, else)
The order of the parenthesis matters greatly in statements like these. If you use an AND statement within an IF statement you have to put each statement of the AND statement within its parentheses and close it before you add the comma and the then statement. Does that help?
The sample usage from this article might help: https://help.smartsheet.com/function/and
-
Give this a try...
=IF(AND(Department2 < >"Ship", [End Date]6 < [End Date]7), "ERROR", "")
-
That got me part way there but I'm still not connecting the dots or I am but don't know it.
From the sample you linked to:
IF(AND(Status1 = "Complete", Status2 = "Complete", Status3 = "Complete"), "All Tasks Complete", "Tasks Incomplete")
To break this down,
The IF statement only refers to the first expression and the "AND" refers to the remainder of the expressions?
- IF Status1="Complete"
- AND Status2 = "Complete"
- AND Status3 = "Complete"
- THEN "All Tasks Complete"
- ELSE "Tasks Incomplete"
Do I have that correct?
-
Not Quite, Because the IF Statement surrounds the and Statements.
=IF ( Opens the IF statement
AND ( Opens the and statement
Status one is complete
Status two is complete
Status three is complete
) Closed the And statement
, THEN
"All Tasks Complete",
ELSE,
"Tasks Complete"
) Closes IF statement
The IF statement is the entire statement and is comprised of three parts, the criteria of the IF statement (In your example it contains the and statement). The then statement (what to put if the criteria are met. And the else statement (what to put if the criteria are not met)
In you example you are saying, if status one, two and three are all complete, return ALL TASKS COMPLETE, but if not return. TASKS COMPLETE. You may want to change your else statement to give you something that more sense like, Tasks not complete, or In Process, or something like that. Whatever makes most sense for you.
-
I'd say you have it pretty well understood. For example, the formula I posted before is essentially saying:
=IF(AND(Department2 < >"Ship", [End Date]6 < [End Date]7), "ERROR", "")
If Department2 does not equal "Ship", and [End Date]6 is less than [End Date]7, then populate the cell with "ERROR". Otherwise leave it blank.
The breakdown of IF/AND is pretty much how you have it here.
