Multiple IF(AND(OR statements to return a value

Options
Kenny T.
Kenny T. ✭✭
edited 08/19/22 in Formulas and Functions

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

Tags:

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 08/20/22
    Options

    @Kenny T.

    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

  • Kenny T.
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!