Welcome!

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

Find more posts tagged with

Comments

Mike Wilday

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

Paul Newcome

Give this a try...

=IF(AND(Department2 < >"Ship", [End Date]6 < [End Date]7), "ERROR", "")

Carl

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?

Mike Wilday

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.

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Copy Rows according to Multi-Dropdown
I have an Intake Sheet in which someone can select multiple Locations and multiple Fiscal Years that the Initiative will occur in. What I need is for this row to copy to the Retail Master Sheet a total of 6 times - one for every possible combination of the selected Location and Fiscal Year. DESIRED RESULT - My current…
I am receiving errorCode: 48 on one of my sheets
I am receiving errorCode: 48 on one of my sheets that is in a Workspace. All other sheets can be accessed except for the most important one. I have created 2 cases for this with no response from Smartsheet: Case# 08756481 Case# 08759594
Edit Smartsheet or Excel Template (can't locate)
I started using this smart sheet for study planning. I work FT and have a demanding study schedule. I needed to add time to the sheet (15 min. intervals would be preferred) but I can't figure out how to adjust it. I also can't locate this template anymore. Uploading for reference. A tally at the end (sum) for total hours…