IF Statement with nested OR
I am trying to write a formula and I am not entirely sure if it is possible. I have 3 columns, Status, Internal only, and Archived. I am trying to write a formula for the status column to mark it Green, Yellow or Red.
If the archived column is blank and the Internal Only column is unchecked, I need it green.
If the archived column is blank and the Internal Only column is checked, I need it yellow.
If the archived column has a date, I need it red.
Is this possible?
Best Answer
-
There's the problem. The checkbox column references the Status column and the Status column references the checkbox column.
You will need to remove one of the formulas.
thinkspi.com
Answers
-
Hi,
In your Status column try:
=IF(ISDATE([email protected]), "Red", IF(AND(IS BLANK([email protected]), [Internal Only]@row=0), "Green", IF(AND(ISBLANK([email protected]), [Internal Only]@row=1), "Yellow", "")))
Does it do what you wanted?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
It tells me the syntax isn't quite right.
-
I had a typo:
=IF(ISDATE([email protected]), "Red", IF(AND(IS BLANK([email protected]), [Internal Only]@row=0), "Green", IF(AND(ISBLANK([email protected]), [Internal Only]@row=1), "Yellow", "")))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Sorry, It is still telling the syntax isn't quite right.
-
ISBLANK and ISDATE should not have a space after IS. The rest looks good. What error are you getting?
=IF(ISDATE([email protected]), "Red", IF(AND(ISBLANK([email protected]), [Internal Only]@row=0), "Green", IF(AND(ISBLANK([email protected]), [Internal Only]@row=1), "Yellow", "")))
Your [Archived] column needs to be a date column. Your [Internal Only] column needs to be a checkbox.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk I really appreciate all your help. Thank you so much.
Ok. I tried it again. I noticed that a couple of my column names didn't match. Someone changed it to Internal Only Use vs. Internal Use, which was causing my 1st issue. It was changed in between the time I posted the original question vs the trying the formula. I fixed those and no longer getting the syntax error message.
Now, the error I get is #Circular Ref in my status column and #blocked in my Internal Use column.
-
Hi Stephanie,
Interesting. So a #blocked error means one of the cells referenced by the formula has an error. What formula are you using in the [Internal Only Use] column?
The #Circular error means the formula references itself. Your Status formula references [Archived] and [Internal Only Use]. Do one of those columns have a formula in them that references [Status]?
You're close to a solution.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I had a formula in a couple status boxes that I meant to delete, which I have done down. It changed the error codes I was getting. The goal is to convert the formula to a column formula once I get it working. There are no formulas in the [Internal Only] check box only and [Archived] is only a date.
I took a couple screen shots on my sheet to show what I am getting and the formula I used.
-
What exactly is in the cell with the circular reference error?
@Mark Cronk We can actually simplify the formula a bit. By using the logic that a nested IF stops on the first true value, we can assume that if it gets to the second IF then then first must be false. That means we can cut out the AND statements where we indicate the Archived column is blank.
=IF([email protected] <> "", "Red", IF([Internal Only]@row = 0, "Green", "Yellow"))
thinkspi.com
-
@Paul Newcome when using your formula suggestion, I get #BLOCKED error in the status column and #CIRCULAR REFERENCE error in the Internal only column.
-
Right. But what exactly is in the cell with the Circular Reference error?
thinkspi.com
-
It is a check box column and it was blank.
This is the formula in the box.
=IF([Status: Live, Internal or Archived]@row = "yellow", 1, 0)
-
There's the problem. The checkbox column references the Status column and the Status column references the checkbox column.
You will need to remove one of the formulas.
thinkspi.com
-
I got it. I removed the check box column and it worked!!!
-
Thanks for the assist @Paul Newcome . Well done. Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!
