Skip to main content

Differentiating between blank and zero

  • February 15, 2017
  • 6 replies
  • 39 views
M

It would be great if there were a way for a formula to differentiate between blank and 0 in a number field. Currently, cellname = "" and cellname = 0 both return true, whether the value is "" or 0.

I understand why this would be the default. It’s like truthy operators in JS (false == 0). But it would be useful if there were an option to circumvent that default behavior, and do a more strict comparison in a particular formula.

Potentially cell = BLANK() would return true for a blank value, but not for a zero value. Currently, equality comparison with BLANK() also returns true for 0.

Alternatively, it could be done with a new function - something like STRICTEQUALS(a, b).

    J
    R
    O

    6 replies

    M
    Forum|alt.badge.img+12
    • Matt_Bush
    • Inspiring
    • February 16, 2017

    For now, you can use COUNTA(cell) which returns 1 when a cell is non-blank (including zero) and 0 if it’s blank.

    We may in the future consider adding an IS_BLANK() function since, as you pointed out, cell = BLANK() isn’t always equivalent and the COUNTA workaround isn’t very obvious.

    J
    R
    M
    M
    • M-Pixel
    • Author
    • New Participant
    • February 16, 2017

    Thanks, glad to know there’s a way to do it already.

    M
    • Mike_Johns
    • New Participant
    • March 13, 2017

    For now, you can use COUNTA(cell) which returns 1 when a cell is non-blank (including zero) and 0 if it’s blank.

    We may in the future consider adding an IS_BLANK() function since, as you pointed out, cell = BLANK() isn’t always equivalent and the COUNTA workaround isn’t very obvious.


    Thanks! I needed this workaround tonight.

    Oli_Steadman
    Forum|alt.badge.img+16
    • Oli_Steadman
    • Known Participant
    • October 25, 2017

    Thanks! I needed this workaround tonight.


    That’s a lifesaving workaround

    J
    • Jeff_Drake
    • New Participant
    • September 15, 2020

    Thank you! This is exactly what I needed!

    P
    Forum|alt.badge.img+6
    • Partyborn
    • Known Participant
    • July 4, 2022

    I also stumbled across this issue. Especially because Field != Blank() always returns true. I don’t think that Blank() should be used, as it is error-prone. Could you please introduce the Is_Blank() function?

    Terms & ConditionsAccessibility statement