Nested IF AND OR Statement

bonzemail

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.

Kelly Moore

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

Kelly Moore

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

bonzemail

That worked like a charm! Thank you, Kelly!