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:
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.
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 .
*** EDIT ***
As of October 2021…
For Numbers columns:
{Col1} = “0” is TRUE if and only if {Col1} is empty
{Col1} = 0 is TRUE if and only if {Col1} contains a zero
{Col1} = “” is FALSE always
For Formula columns:
{Col1} = “0” is TRUE if and only if {Col1} returns “0” as a string
{Col1} = “” is TRUE if and only if {Col1} returns empty
{Col1} = 0 is TRUE if and only if {Col1} returns a zero (numeric value)
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.
Sorry this was giving you a bit of trouble! I’ve decided to test this a bit further on my end, and it seems like the error relates to using math operators, and not the monday.com functions like DIVIDE, for example.
To illustrate this further, the following formula will work correctly:
IF({Value Budget}<>“0”,MULTIPLY(MINUS({Value Budget},{Cost of Sale}),DIVIDE(12,{Repeat Months})),0)
Hi, how do i return a Zero from a blank cell. Currently i get a red illegal formula “!” displayed when a number is not present. I need to default the column to “0” when its first created, is this possible?
Thanks
The easiest way to set a default value for a column is to use the “Item default values” settings on the main board screen. It is just below the main board headers, kind of looks like a pencil above paper:
