# Formula Syntax Error

• 1
• Question
• Updated 5 months ago
Hello:

I am having difficulty with a formula syntax.  I am pretty green, and sadly my skillset has some "developmental opportunities."

I am trying to do some calculations for equipment rental based on days utilized (creating a minimum, daily, weekly, and monthly rates).

Attached is my formula.  I know I have made a bonehead mistake.

Help please? • 850 Points Posted 5 months ago

• 1
• 72,166 Points Happy to help, but please post a copy and paste of the actual code and not just a screen shot.  I can't edit a screen shot.  Also please do the same for the error message.
• 850 Points Hi:

Here is what I have.

Thank you,

Dirk

Formula syntax error

There are extra characters beyond the end of the formula

If(
[Days]=0.5, ([Equipment Rate - Base Cost]/96),
([Days]>0.5 and [Days]<=4), ([Days]*[Equipment Rate - Base Cost]/24),
([Days]>=4 and [Days]<=7), ([Equipment Rate - Base Cost]/24)*3)
([Days]>=8 and [Days]<=32), ([Equipment Rate - Base Cost]/24)*8),
)

• 72,166 Points You do not need the very last comma.that will fix the syntax error.

But you can also simplify your formula like this

If(
[Days]=0.5, [Equipment Rate - Base Cost]/96,
[Days]>0.5 and [Days]=4 and [Days]=8 and [Days]<=32,[Equipment Rate - Base Cost]/24*8
)

It is not a syntax error to have those extra brackets but you can decide if it makes the formula more or less readable.
• 1,570 Points Shouldn't it be more like this?
`If([Days]=0.5, [Equipment Rate - Base Cost]/96,[Days]>0.5 and [Days]<=4,  ([Equipment Rate - Base Cost]/24)*[Days],[Days]>=4  and [Days]<=7,  ([Equipment Rate - Base Cost]/24)*3,[Days]>=8  and [Days]<=32, ([Equipment Rate - Base Cost]/24)*8)`
• 818 Points Dan:

This worked perfect.  Thank you!

Dirk
• 818 Points Dan:

Kind of a similar situation.... but I am stymied again:

Formula:
If([Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),

Error Message:
Formula syntax error
A closing parenthesis is missing.

If([Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours]  ,
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]

If certain fields have certain content, a specific field is used (in the same table) to calculate labor fees.

Any insight is greatly appreciated!!!
(Edited)
• 72,166 Points I was just trying to help with the syntax error. I did not try to think through the intent of the formula. So you may be right in your 2nd line where you multiply by [Days], but then the last two lines would have to be similarly adjusted.
• 1,570 Points Gotcha. I'm not clear on the intent, either...but the "[Days]>0.5 and [Days]=4 and [Days]=8 and [Days]<=32" bit in your example stuck out to me. ;)
• 72,166 Points Ok sorry, now I see that when I posted from my ipad some of my formula got lost.

So Dirk, just get rid of your last comma and your formula will work from a syntax point of view.

But whether you get the calculated results you were hoping for is another matter.

If that is the case and you can’t figure it out then you’ll have to say in words what your formula is supposed to do and we can help you construct the logic to get there.
(Edited)
• 3,728 Points my skillset has some "developmental opportunities."

Brilliant
• 72,166 Points When you have a formula this long there is a good indication that your app should be redesigned to have a table of rates and lookup the rate from the table.

My suggestion is to make a table with a hyphenated Key field like
Labor Category-Status-Hours Category.  It will get populated by a form rule which refers to a formula which calculates that Hyphenated value from the drop down multiple choice fields.

You would be better served with more spacing of your formula.  I first use a formula variable to calcuate the Rate.

var number Rate =

If(
[Associate - Labor Category]= "Carpenter"
and [Staff Status]= "Dedicated"
and [Hour Category]= "Standard",[Labor Rate - Dedicated Standard Rate],

[Associate - Labor Category]= "Carpenter"
and [Staff Status]= "Dedicated"

[Associate - Labor Category]= "Carpenter" and
[Staff Status]= "Variable"
and [Hour Category]= "Standard",[Labor Rate - Variable Standard Rate],

etc

[Associate - Labor Category]= "Superintendent"
and [Staff Status]= "Variable"

// now we refer to the rate and multiply by the hours

\$Rate * [Hours]

~~~~~~~~~~~~~~~~~

btw, the other advantage of using a table is that once you lookup the rate you can use a snapshot field to freeze it so that next year when the rates may change, you will not affect the historical records.

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html

• 850 Points I can appreciate that.... I am just in a crunch to get things operational.  I'm just a regular working guy (construction guy), so I am swimming a bit above my depth.

I do have a contract based rate system.  So labor rates are entered under a contract number.  So this should not impact rates on previous rate plans.

Its nice to know about snap shot fields, I will try to incorporate into my working knowledge.

Thank you so much

Dirk
• 850 Points I don't think so.  This comes from another table that is part of this specific 2019 contract.  The 2020 contract would have a different value.
• 850 Points I must be doing something wrong:

Formula syntax error

Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.

var number Rate =
If(
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Dedicated" and [Hour Category]= "Standard",[Labor Rate - Dedicated Standard Rate],
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Dedicated" and [Hour Category]= "Premium",[Labor Rate - Dedicated Premium Rate],
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Variable" and [Hour Category]= "Standard",[Labor Rate - Variable Standard Rate],
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Variable" and [Hour Category]= "Premium",[Labor Rate - Variable Standard Rate],
// now we refer to the rate and multiply by the hours
\$Rate * [Hours]

• 72,086 Points np, you just need to close the final bracket to match the opening bracket on the IF

[Hour Category]= "Premium",[Labor Rate - Variable Standard Rate])﻿

// now we refer to the rate and multiply by the hours
\$Rate * [Hours]

• 850 Points Got it!  Yes... problem solved.

Thank you so much!
• 72,086 Points Great, thx for letting me know.