IF/AND/OR Nested Health for Checkboxes

Options

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!

Tags:

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Options

    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
    Tina Rustvold ✭✭✭✭✭
    Options

    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
    Stefan ✭✭✭✭✭✭
    Options

    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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Tina Rustvold
    Tina Rustvold ✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!