Help creating a formula
Can someone help me fix the following formula?
# of Travel Trips = IF(([Onsite Delivery]@row = "Onsite" , [Software Installation Hours]@row >40, 1, or <4179, 2, or <80, 3) * [Number of Sites]@row))
Not sure what to do with the ORs...or how to include them all into the formula. Do I have too many parenthesis?
Best Answers

@Stacy Stoffel , try removing the 2 end parenthesis at the end of the formula. That should do the trick.

Oh! I'm so sorry  let's try this again:
=if([onsite delivery]@row<>"Onsite",0,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=40),1,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=79,2,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row >80,3,""))))*[Number of Sites]@row
I just changed the "=0" to '<>"Onsite"'. It means if Onsite Delivery does not equal Onsite, display 0.
Let me know if that fixes it.
Answers

Hi Stacy,
I think I understand what you're asking for  try this:
=if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=40),1,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=79,2,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row >80,3,"")))*[Number of Sites]@row
This says If Onsite Delivery is set to Onsite AND...
Software installation hours is less than or equal to 40, show 1
Software installation hours is between 41 and 79, show 2
Software installation hours is greater than or equal to 80, show 3
(Otherwise, show 0)
Then multiply the number shown by the Number of Sites in this row.
Hope this helps  let me know if I totally missed the mark on what you were looking for.
Best,
Heather

What am i missing? I keep getting Unparsable. Do I have commas in the wrong places?

@Stacy Stoffel , try removing the 2 end parenthesis at the end of the formula. That should do the trick.

Thank you Heather! You are awesome!!! Now I can fully automate this sheet with document builder. No manual entries required! :D

Hooray! So glad it worked for you. :)

One more question Heather, if I want the answer to be 0 when Onsite is NOT selected, where do I put that in the formula?

Add this in between the first = and if:
if([onsite delivery]@row=0,0,
Then add another end parenthesis before *[number of sites]@row. So it will look something like this:
=if([onsite delivery]@row=0,0,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=40),1,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=79,2,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row >80,3,""))))*[Number of Sites]@row
(changes highlighted in bold)

still not working....

Try removing the end parenthesis at the end of the formula.

that changed it from unparsable to invalid operation

Oh! I'm so sorry  let's try this again:
=if([onsite delivery]@row<>"Onsite",0,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=40),1,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row <=79,2,if(AND([Onsite Delivery]@row = "Onsite",[Software Installation Hours]@row >80,3,""))))*[Number of Sites]@row
I just changed the "=0" to '<>"Onsite"'. It means if Onsite Delivery does not equal Onsite, display 0.
Let me know if that fixes it.

yay!!! that worked. Thank you again for all your help!

Hooray! Happy to help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 359 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!