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 <41-79, 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
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!