Multiple IF(AND(OR statements to return a value
I am trying to find the correct formula for the following:
IF classification is External Hire, AND Business Title is Assistant Store Manager OR Store Manager, AND Store Format is Steady State, THEN training duration = 42
IF classification is External Hire, AND Business Title is Assistant Store Manager OR Store Manager, AND Store Format is Pre-Launch, AND Geo is Existing OR Extended THEN training duration = 49
IF classification is External Hire, AND Business Title is Assistant Store Manager OR Store Manager, AND Store Format is Pre-Launch, AND Geo is New THEN training duration = 52
IF classification is Internal Hire, AND Store Format is Pre-Launch or Steady State, AND Geo is Existing OR Extended THEN training duration = 42
Answers
-
I like to say Nested IF's are like cutting a block of Cheese..
You go through all the scenarios cutting off bits as you go through all the scenarios
The interpretations are..
Note: I made an assumption about New Hires that are not "Managers" in the second case
The if statement that is built becomes
=IF([External Hire]@row = "No", 42, IF(OR([Business Title]@row="Assistant Store Manager",[Business Title]@row="Store Manager"), IF([Store Format]@row = "Steady State", 42, IF([Geo]@row="New", 52, 49)), 42))
Now for a more advanced class
In cases like these, I like to use VLOOKUPS with a Matrix.
I would create a training matrix Grid with a Helper Column
The helper column is pretty simple with one quirk as External Hire is a checkbox, I could use a Yes or no but then I also need to account for the user leaving it blank which creates the 3rd scenario
So in the case of the helper column, I Simply make one big text string
=IF([External Hire]@row = 1, "Y", "N") + [Business Title]@row + [Store Format]@row + Geo@row
You can see how I had to do a bit of logic on the External hire to switch it to a Y or N
Now I create the same helper column in the Data Sheet
Then I link the Data sheet to the Matrix with a VLOOKUP
=VLOOKUP(Helper@row, {Training Matrix}, 6, false)
so this matches the two helper rows and then looks up the training duration (stored in column 6 of the matrix)
Now you can see a problem down with Chad Brooks or Brian Jones as those Scenarios are not in the Traning Matrix..
You could load all of them in or make an assumption that if they are not in the table they should all get 42 units of training.
So Say Chad Brooks as a Sales Associate at a Pre-Launch New store would get 42 units, same as David Brooks an Internal Hire Store Manager at a Pre-Launch Extended Store
The Hybrid formula that would do this is below and you can see the results in the Training Duration HYBRID column
=IF(ISERROR(VLOOKUP(Helper@row, {Training Matrix}, 6, false)), 42, VLOOKUP(Helper@row, {Training Matrix}, 6, false))
Basically, if there is an error, which you can see in the VLOOKUP column as a #NO MATCH ,we will assume 42 units
The beauty in this is that as your scenarios change..
Someone says Hmm no David Brooks should have 49 units of training even if he was an internal hire you simply have to create that in the matrix and it all works
and Voila
Hope this gives you more than you need and doesn't blow your brain !!
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Thank you! I haven't been able to get this figured out, and forgot to check the responses. I appreciate the help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!