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
-
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.
-
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!
-
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!
-
Will try this tomorrow Paul. Thanks for the tip!
-
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives