IF/AND/OR Nested Health for Checkboxes

Tina Rustvold

Thank you Community as I thought I had this from all your previous questions, but I am still receiving an Incorrect Argument Set error for this one.

=IF(AND([Database Complete]@row, 1, [Enabled Interface]@row, 1, [Charges Flowing]@row, 1), "Green", IF(OR(AND([Database Complete]@row, 0, [Enabled Interface]@row, 0, [Charges Flowing]@row, 0), "Red", "Yellow")))

Show Green if the three columns are checked, show Red if they are unchecked, and Yellow for any other combination. Or do I need to set my Health column with a default status of Yellow, then allow the formula to work for one absolute or the other (red, green). Thanks!

Sameer Karkhanis

Try this:

=IF(AND([Database Complete]@row, [Enabled Interface]@row,[Charges Flowing]@row), "Green",
	IF(NOT(AND([Database Complete]@row, [Enabled Interface]@row, [Charges Flowing]@row)), "Red", "Yellow"))

Tina Rustvold

It cleared up the error, but only the Green (all columns checked) or Red (all columns unchecked) health ball is triggered. If I have only one or two columns checked, the Yellow does not appear. I was just looking for some way to show an in progress status. This could still work but curious how to get Yellow working.

Stefan

Hi @Tina Rustvold,

try this one:

=IF(AND([tick 1]@row = 1, [tick 2]@row = 1, [tick 3]@row = 1), "green", IF(AND([tick 1]@row = 0, [tick 2]@row = 0, [tick 3]@row = 0), "red", "yellow"))

Replace the "Ticks" with your column names.

Hope this helps!

Stefan

Tina Rustvold

We still can't get Yellow to work in this column formula, but are good having just a Red or Green result. However, we no hae an additional column to be considered for this status. I tried using an OR within the forumla and got an Incorrect Argument message, so I feel I am close:

=IF(AND([Database Complete]@row, [Enabled Interface]@row, [Charges Flowing]@row, OR([No RCM Changes]@row, 1), "Green", IF(NOT(AND([Database Complete]@row, [Enabled Interface]@row, [Charges Flowing]@row)), "Red", "Yellow")))

Understaning IF function will stop at the first true statement, the first three columns would not be checked-ever-if the No RCM Changes column is checked. So I tried just adding it to the AND string but received the same message.

Genevieve P.

Hi @Tina Rustvold

With AND and OR functions you'll need to put the criteria as a statement within it, like in Stefan's formula:

[tick 1]@row = 1

or

[Database Complete]@row = 1

With your additional criteria, is this another checkbox that has to be checked along with the others? If so, youcan include it in the AND statement:

=IF(AND([Database Complete]@row = 1, [Enabled Interface]@row = 1, [Charges Flowing]@row = 1, [No RCM Changes]@row, 1), "Green"

for a full formula:

=IF(AND([Database Complete]@row = 1, [Enabled Interface]@row = 1, [Charges Flowing]@row = 1, [No RCM Changes]@row, 1), "Green", IF(AND([Database Complete]@row = 0, [Enabled Interface]@row = 0, [Charges Flowing]@row = 0), "Red", "Yellow"))

Cheers,

Genevieve