Help creating a formula

Stacy Stoffel

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?

Heather Duff

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

Heather Duff

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.

Heather Duff

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

Stacy Stoffel

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

Heather Duff

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

Stacy Stoffel

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

Heather Duff

Hooray! So glad it worked for you. :)

Stacy Stoffel

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?

Heather Duff

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)

Stacy Stoffel

still not working....

Heather Duff

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

Stacy Stoffel

that changed it from unparsable to invalid operation

Heather Duff

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.

Stacy Stoffel

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

Heather Duff

Hooray! Happy to help.