IF function are incorrectly calculating number comparisons

Platform discussions
1

IF function is erroring out on number comparisons.

Steps to reproduce:

  1. Create two Number columns named “Total” and “Completed”
  2. Create a Formula column with the following formula:
    IF(AND({Completed}>=0, {Total}>0), {Completed} / {Total}, 0)

Expected Result:

  • If “Total” column is 0, less than 0, or empty, the formula column should result in a 0.
  • If “Total” column is greater than 0, the formula column calculates (Completed/Total).

Actual Result:

  • If “Total” column is greater than or less than 0, the formula column calculates correctly - WORKS
  • If “Total” column is 0 or empty, the formula column will show an error icon - FAILS

My best guess is:

  1. Monday has a compiler bug (i.e. order of operations error) where they’re evaluating each statement in the IF function regardless of whether or not the IF statement conditional logic has been met, and/or,
  2. Monday has a perhaps incomplete/limited value casting implementation (e.g. they are not automatically casting empty field values to FALSE, or IF conditional evaluation errors are not being automatically cast to FALSE, etc)

Or something else?

2

Hi OJ,

It is called an “eager” approach of the IF function. See the 2nd paragraph of A Simple Way to Handle Empty Columns and Avoid Errors in your Formulas for the full explanation.

Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*

3

Yeah, that’s what my first-guess above was… and what I’m suggesting is that eager loading is a rather misguided algorithmic approach for a conditional function. The whole point of a conditional is to be, well, conditional. If I’m expected to have to handle process control within a scripted environment, well, that’s pretty lackluster, right? Microsoft Excel, Google Sheets, Apple Numbers, etc – none of those spreadsheets do it that way because eager loading doesn’t really make sense within this context.

Anyhow, sounds like I’m out-of-luck on this one… hope that Monday devs revisit the implementation here to make conditional functions a bit more sane / user-friendly.