IF Statement for Invoice

Platform discussions
1

LOGIC
If a qty changed column is blank, calculate total cost by multiplying original number * Unit Price.
If changed column has a number in it, calculate total cost by multiplying changed number * unit price

Can anyone please tell me why this formula does not work?
FORMULA
IF ({QTY Changed by Site Super}= “0”, MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{QTY Changed by Site Super}),MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{Original Qty #7 Weep Sill Screed})

Many thanks

2

Hi Sue,

You have more open parentheses than close parentheses. Add a close parenthese at the end and it should work.

You can check the syntax of your formulas for parenthesis and curly braces here:
https://docs.mdboosters.com/check-your-formula

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

3

Hi Gilles
I love your formula checker. I tried it before I made the change and I got the same error.
Then I made the change, and it still isn’t working in Monday.
So I tested it again and got this

image
image854×497 17.7 KB

Here is the new one I tested.
IF ({QTY Changed by Site Super}= “0”, MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{QTY Changed by Site Super}),MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{Original Qty #7 Weep Sill Screed}))

In reading one of the blogs on your site, I think it has to do with a blank/zero in the changed quantity column. Any ideas?

Many thanks

4

In monday, are you still getting an error or it is not giving you the result you expect?

5

I’m still getting the red error icon

6

Test your formuly elements by elements:

MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{QTY Changed by Site Super})

Then if it works:

MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{Original Qty #7 Weep Sill Screed})

Then if it works:

IF({QTY Changed by Site Super}= "0", MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{QTY Changed by Site Super}),MULTIPLY({#7 weep sill screed 1 and three eights by 3-1/2 Flange Galv10},{Original Qty #7 Weep Sill Screed}))

This should allow you to narrow it down.

7

Thanks! I’ll let you know!

8

Wow - No joy!

image
image902×829 38.3 KB

9

The Check the formula page is just checking the number of open/close parentheses and braces.

Did you test each element individually? Do they work?