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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!