Nested IF AND OR Statement

Good day!


I have a list of tests for my team to take, and would like the end column to reflect the name of each test they have passed.

This is what I have tried and cannot seem to get it to work.

=IF(AND(OR([DTX Tier 1 Mastery - Score]@row >= 0.9, "DTX Tier 1"),(OR([DTX Tier 2 Mastery - Score]@row >= 0.9, "DTX Tier 2"),(OR([eTrans Mastery - Score]@row >= 0.9, "eTrans"),(OR([eCentral Mastery - Score]@row >= 0.9, "eCentral"), "")))))

I have tested each individually, I just cannot get them to work as a whole.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hi @bonzemail

    A nested IF will stop at the first true statement it finds which is one reason individually written if statements work but the combination would not work.

    One approach will be to add 4 contiguous (all side by side) helper columns, one for each test. These could be all the way to the right of the sheet and hidden if desired. These use each of your IF statements, respectively

    For example, in [DTX Tier 1 Helper] column

    =IF([DTX Tier 1 Mastery - Score]@row >= 0.9, "DTX Tier 1")

    Repeat the formulas respectively in each of their helper columns, one formula per helper column

    Then in your [TEST Fx] column

    =JOIN(COLLECT([DTX Tier 1 Mastery - Helper]@row:[eCentral Mastery - Helper]@row,[DTX Tier 1 Mastery - Helper]@row:[eCentral Mastery - Helper]@row, @cell<>""),",")

    cheers

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hi @bonzemail

    A nested IF will stop at the first true statement it finds which is one reason individually written if statements work but the combination would not work.

    One approach will be to add 4 contiguous (all side by side) helper columns, one for each test. These could be all the way to the right of the sheet and hidden if desired. These use each of your IF statements, respectively

    For example, in [DTX Tier 1 Helper] column

    =IF([DTX Tier 1 Mastery - Score]@row >= 0.9, "DTX Tier 1")

    Repeat the formulas respectively in each of their helper columns, one formula per helper column

    Then in your [TEST Fx] column

    =JOIN(COLLECT([DTX Tier 1 Mastery - Helper]@row:[eCentral Mastery - Helper]@row,[DTX Tier 1 Mastery - Helper]@row:[eCentral Mastery - Helper]@row, @cell<>""),",")

    cheers

  • That worked like a charm! Thank you, Kelly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!