IF Statement with nested OR

Stephanie Tran

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?

Paul Newcome

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.

Mark Cronk

Hi,

In your Status column try:

=IF(ISDATE(Achived@row), "Red", IF(AND(IS BLANK(Archived@row), [Internal Only]@row=0), "Green", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=1), "Yellow", "")))

Does it do what you wanted?

Mark

Stephanie Tran

It tells me the syntax isn't quite right.

Mark Cronk

I had a typo:

=IF(ISDATE(Archived@row), "Red", IF(AND(IS BLANK(Archived@row), [Internal Only]@row=0), "Green", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=1), "Yellow", "")))

Mark

Stephanie Tran

Sorry, It is still telling the syntax isn't quite right.

Mark Cronk

ISBLANK and ISDATE should not have a space after IS. The rest looks good. What error are you getting?

=IF(ISDATE(Archived@row), "Red", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=0), "Green", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=1), "Yellow", "")))

Your [Archived] column needs to be a date column. Your [Internal Only] column needs to be a checkbox.

Mark

Stephanie Tran

@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.

Mark Cronk

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

Stephanie Tran

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.

Paul Newcome

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(Archived@row <> "", "Red", IF([Internal Only]@row = 0, "Green", "Yellow"))

Stephanie Tran

@Paul Newcome when using your formula suggestion, I get #BLOCKED error in the status column and #CIRCULAR REFERENCE error in the Internal only column.

Paul Newcome

Right. But what exactly is in the cell with the Circular Reference error?

Stephanie Tran

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)

Paul Newcome

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.

Stephanie Tran

I got it. I removed the check box column and it worked!!!

Mark Cronk

Thanks for the assist @Paul Newcome . Well done. Mark