Multiple IF/AND

Need help. I'm setting multiple conditions to a formula, and I can't seem to get it right.

My goal is to auto-calculate our development hours based on the material type.

Say:

If the material is Instructor-Led, and new - development hours should be duration hours/60*5.63, if existing, then development hours should be duration hours/60*2.83

Another is if it's an e-learning, and new - development hours should be duration hours/60*11.25, if existing, then development hours should be duration hours/60*7.5


Here's my formula at least for the first argument:


=IF(AND(Delivery2 = "ILT", Type2 = "New", [Module Duration (mins)]2 / 60 * 5.63, IF(AND(Delivery2 = "ILT", Type2 = "Existing", [Module Duration (mins)]2 / 60 * 2.82, "-"))))


This will be a life saver.

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @SandyAbegail,

    try this:

    =IF(AND(Delivery3 = "ILT", Type3 = "New"), [Module Duration (mins)]3 / (60 * 5.63), IF(AND(Delivery3 = "ILT", Type3 = "existing"), [Module Duration (mins)]3 / (60 * 2.82, "-"))

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Thanks Stefan. This time i got an #UNPARSEABLE error.

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @SandyAbegail ,

    hmm,

    • have you tried using @row instead of row numbers (see below)
    • is the column with the formula "text/numbers" ?

    =IF(AND(Delivery@row = "ILT", Type@row = "New"), [Module Duration (mins)]@row / (60 * 5.63), IF(AND(Delivery@row = "ILT", Type@row = "existing"), [Module Duration (mins)]@row / (60 * 2.82, "-"))

    Sometimes Smartsheet doesn't like formulas copied into cells. Try removing the last bracket and have Smartsheet complete the formula.

    Hope this helps

    Stefan

    PS: This is how it looks in my test sheet. Never mind european style format.


    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Hi,

    Yes, the columns is "text/numbers".

    Both gives me the same error. I really appreciate you helping me on this.


  • Finally figured it out. I missed closing the IF statement. :)


    Here's my formula now:


    =IF(AND(Delivery@row = "ILT", Type@row = "New"), [Module Duration (mins)]@row / 60 * 5.63, IF(AND(Delivery@row = "ILT", Type@row = "Existing"), [Module Duration (mins)]@row / 60 * 2.83, IF(AND(Delivery@row = "WBT", Type@row = "New"), [Module Duration (mins)]@row / 15 * 11.25, IF(AND(Delivery@row = "WBT", Type@row = "Existing"), [Module Duration (mins)]2 / 15 * 7.5))))


    Thanks a lot Stefan!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also condense this formula a bit like so...

    =[Module Duration (mins)]@row / 60 * IF(Delivery@row = "ILT", IF(Type@row = "New", 5.63, 2.83), IF(Type@row = "New", 11.25, 7.5))


    This consolidates a lot of the repeating data points/requirements and saves quite a few keystrokes (and less back-end work for the sheet too).

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Will try this tomorrow Paul. Thanks for the tip!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com