Need help creating a formula. I want to add column A and column B, then subtract that total from column C and report the result, but if the result is less than zero, then return zero as the result. Use case is: Adding items in inventory plus items on order from supplier, then subtract that total from total current orders from customers for that item. Result is the amount of items we still need to order. I’d rather see zero to indicate we’re covered than have to see a negative number.
Thanks in advance for any help here.
Hi @mikeanan1,
Try:
IF({Column C}-({Column A}+{Column B})>0,{Column C}-({Column A}+{Column B}),0)
Wonder how this same code would be written when using the Advanced Formula Booster, the 3rd-party app that revolutionized formulas in monday?
AFB does not use the formula column at all. It casts the results of the formulas to regular columns. Here we added a {Result} column to hold the result of the formula.
With AFB, you can use up to 100 lines in your formulas. Here we use 3.
- In the first line, we sum {Column A} and {Column B} and store the result in a temporary variable named [Sum].
- In the second line, we set the {Result} column to the the subtraction from {Column C} of our sum (AFB does not use the Formula column, but rather cast formula results to any type of columns )
- In the 3rd line, we check if the {Result} column is below 0. If it is, we set it to 0.
Lines are processed one after the other, but only the final result is really cast to the column, so decomposing the formula in Line 2 and Line 3, instead of using a single line as the suggested formula in monday has no impact in terms of speed, but it does make the formula simpler to read.
The {Result} column being a regular Numbers column, you can use it anywhere and don’t have to deal with the limitations of the formula column.
Gilles, that worked perfectly. And now, I can follow the logic in how to script these going forward. Thank you!