# 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?

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭
edited 04/13/21

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!