Automation with OR

Paul Newcome

@Debbie Sawyer You do have the correct idea for the typical nested IFs. I definitely used a non-typical logic, but it is more a matter of personal preference really.

When you nest like this:

=IF(this is true, IF(that is true, .....

That is the same as :

=IF(AND(this is true, that is true), .....

In most smaller cases I do use the AND statement, but that would get out of control pretty fast when we are talking about a lot of checkboxes, or I work backwards.

The logic I used below has a few perks (bold below):

=IF([Checkbox1]@row = 1, IF([Checkbox2]@row = 1, IF([Checkbox3]@row = 1, IF([Checkbox4]@row = 1, "Status 4"), "Status 3"), "Status 2"), "Status 1")

Allows us to basically work backwards without having to worry about multiple AND functions.

If Box 1 is checked then we evaluate to see if Box 2 is checked. If it is then we evaluate to see if Box 3 is checked, so on and so forth. Then our status outputs work backwards, so if they are all checked meaning 1, 2, 3, and 4 above, then we output 4. If 4 is not checked then it will output the "value if true" for the Box 3 argument, and back on down the line.

It is very similar to if you had used logic for checked boxes and reversed the order to start at the end and work your way backwards. For me though it is easier to read and manage. I have my boxes in order, my statuses are in reverse order, but the boxes are grouped together and the outputs are grouped together. I also don't have to worry about counting those pesky closing parenthesis at the end since I just put one after every output.

However... I did miss a step with the transition from the box group to the output group. The value if true for the last box being checked would be the last status. In the below example it would be "Complete" then work backwards to show what the next step is (as opposed to the last step completed).

=IF([Checkbox1]@row = 1, IF([Checkbox2]@row = 1, IF([Checkbox3]@row = 1, IF([Checkbox4]@row = 1, "Complete", "Status 4"), "Status 3"), "Status 2"), "Status 1")

The above could also be accomplished using standard nested IF logic like so:

=IF([Checkbox4]@row = 1, "Complete", IF([Checkbox3]@row = 1, "Status 4", IF([Checkbox2]@row = 1, "Status 3", IF([Checkbox1]@row = 1, "Status 2", "Status 1"))))

or

=IF([Checkbox1]@row = 0, "Status 1", IF([Checkbox2]@row = 0, "Status 2", IF([Checkbox3]@row = 0, "Status 3", IF([Checkbox4]@row = 0, "Status 4", "Complete"))))

.

All 3 versions have the same character count. It is just a different way of looking at them. I just like the method I used a little better because my arguments are grouped together and my outputs are grouped together (makes it easier for me to read), and the closing parenthesis are easier for me to manage.

Debbie Sawyer

@Paul Newcome

Thank you so much for explaining that. I hadn't come across that way of grouping expressions and arguments together before.

I intervened this time as the formula wasn't parsing with the way it was being entered and having not come across that method, I couldn't explain why it didn't parse. Hence describing the other way!

But I am always open to learning so thank you for explaining! 😁

Kind regards

Debbie

Paul Newcome

@Debbie Sawyer I didn't even look into the parsing issue with the second attempt to use my logic because by the time I go tot it, you had already provided a working solution. My guess would have been the extra space between the end of the column name and the closing square bracket here:

IF([PE - Rework/Modification complete ]@row=1,

But it worked out anyway because I had missed that one little piece right in the middle when we transition from arguments to outputs. That wouldn't have caused an error message, but it would have not worked on the second to last status. Using my 1, 2, 3, 4 example above, it would have worked just fine for 1, 2, and 4 but left blank for 3. TO get it to work for 3 is where we needed that piece at the transition.