Multiple IF/AND

SandyAbegail

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.

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

SandyAbegail

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

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.

SandyAbegail

Hi,

Yes, the columns is "text/numbers".

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

SandyAbegail

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

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).

SandyAbegail

Will try this tomorrow Paul. Thanks for the tip!

Paul Newcome

Happy to help. 👍️