formulas What is wrong with this formula, trying to get either or as the dumpster price? If([20 Yard Dumpster]=true,350), If([30 Yard Dumpster]=true,400)

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Photo of MARY

MARY

  • 0 Points

Posted 3 years ago

  • 1
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Try this:

If([20 Yard Dumpster], 350,
 If([30 Yard Dumpster], 400)
)

I assume both of your fields are Checkboxes so there is no need to test if they are equal to the literal value true. The above formula will test the [20 Yard Dumpster] field first and will not further apply any logic if it is true. Also, if both fields are false the formula will not return a default value.
Photo of Laura

Laura

  • 20 Points
Your parentheses were in the wrong place.

You could also have written this as a Case statement

Case(true,

[20 Yard Dumpster]=true,350,

[30 Yard Dumpster]=true,400

)

Alternatively, if you are making a "choice", you  might instead want a different field selection such as [Dumpster Type] with the options "20 Yard" and "30 Yard"; rather than having two separate fields which can both be filled in.  In this case your formula would be:

Case([Dumpster Type],

"20 Yard",350,

"30 Yard",400

)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
My formula works with the parentheses as is.

If you use a Case() function you can omit the =true part, See:

Case(true,
  [20 Yard Dumpster], 350,
  [30 Yard Dumpster], 400
)
Photo of AngelaMastro

AngelaMastro

  • 42 Points
Hello. I tried this formula, and it will only return the first value. The reason is because more than one statement can be true. Example:

Case(true, [Job Released], 10, [Paint Complete], 70)

Both can be true, but I want the field to return the value for the statement that has a higher value.
How do I do this? I am fine with using multiple if statements, but just don't know how to tell it that more than one statement can be true and to return "x" value if so.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
When you want to prioritize the answer, make that the first evaluation in your daisy chain of IF statements.

If([Job Released]=true, 10,
If([Paint Complete]=true, 70, null))

If Job Released is true it will show that answer, and wont even check the rest of the formula.  You can continue this as long as you'd like.

Good luck
Photo of AngelaMastro

AngelaMastro

  • 42 Points
Thank you! that was very helpful and worked.