Formulas: "doesn't equal", "is number", "is blank/null", nested IFs

Hi everyone!

This is a question related to formulas on Monday.com.

Use case

I have a column (Col 1) that will either contain a number, or nothing. The presence of a number is a visual signal to the human users, so I don’t want to default to 0.

I might have a formula column (Col 2) looking at Col 1 with this formula:

IF({Col 1} > 7, TRUE, FALSE)

There’s no issue if the cell being evaluated in Col 1 contains a number. If the cell is blank, I get an “Illegal formula” error with a little info icon in the affected cells.

This isn’t show-stopping, but it adds visual clutter that I’d like to reduce for my users.

I know how I’d deal with this in a spreadsheet, but am not sure what Monday provides to help in this situation.

Solutions I’ve looked for

With formulas on Monday, is there support for any of the following:

  • doesn’t equal
  • is number
  • is blank/null
  • nested IFs

I browsed through these documents, but haven’t found what I’m looking for:

  1. https://support.monday.com/hc/en-us/articles/360001235445-The-Formula-Column-Explanation-and-Use-Cases
  2. https://support.monday.com/hc/en-us/articles/360001276465-List-of-all-available-formulas

In general, avoiding errors in monday formulas can sometimes be tricky.

For example, to eliminate divide by zero errors, something like this would make sense:

IF({number}<>0,1/{number},"")

But this does not work. All portions of the formula must ALWAYS be valid. This DOES work:

IF({number}<>0,1/IF({number}<>0,{number},1),"")

However, I suspect that the issue you are currently experiencing may be related to how you are accessing the board. I ran into a similar situation where one of our users was seeing an error like this when no one else was. The issue was that she was using the “old” version of Microsoft Edge. When we had her update to using the new chrome based version of Edge, everything worked.

1 Like

Another note… With regard to the is it zero or is it null/blank:
“IF({Col1}=0,TRUE,FALSE)” will return TRUE when the value of {Col1} is 0 AND when the value of {Col1} is null/blank.
“IF({Col1}=“0”,TRUE,FALSE)” will return TRUE ONLY when the value of {Col1} is 0 .

1 Like

Thanks @JCorrell! I’ll try these out.

Interesting note about browser support. For the record, I’m always running the latest version of Safari.

My first computer was an Apple. I know next to nothing about them now. You might want to have one of your MS based users try viewing the formula. I did try your initial formula on my system and it worked without problem. Currently, all formula functions on monday are evaluated on the client side. So, it can be a bit more susceptible to browser issues and differences.