Can't Escape Hidden Zeros Everywhere

English
#1

Hi All,

Wonder if anyone can help me.

I’ve been working with ChatGPT to build some spreadsheets, and between us we can’t figure out why all the cells in my workbook seem to exhibit the behaviour as if there is a hidden “0” in all of them.

For instance, if I pick a random empty cell AL21, then:
If I enter this formula: =ISNUMBER(AL21), it returns FALSE
Yet trying this formula =AL21=0 returns TRUE!?!

And any formula I create such as =COUNTA(AL21:AL100) when all cells are “supposedly” empty, it returns an output of “1”.

I’ve tried every way that myself and ChatGPT can think of to “clear” the cells fully, but nothing seems to work.

Any help anyone can offer would be much appreciated.

Kind regards.

CT

#3

…So tell your ChatGPT: »its rather stupid to enter such Formulas«:

  • into the very same cell where the formula refers to
  • into some cell inside the cellrange where the formula refers to
#4

Hi @karolus,

Thanks for the reply.

Sorry, that was my fault. I didn’t mean to describe trying to enter a formula into a cell that’s in any way self referencing.

Obviously I’m pointing the output to a different cell, it was just a simple test to try and pick a random cell from the workbook to describe the problem, namely every cell I run “=ISNUMBER” on returns FALSE and yet if I run “=0” on the same cell it returns TRUE!

Then a formula like "=COUNTA(… returns a “1” even though all cells show as empty. Even after I try all different formatting optons and “Delete” and “Clear Contents” etc.

Cheers.

CT

#5

It doesnt matter, if the formula returns 1 then there is somewhere in the observed cellrange eg. a »space« or a formula like ="" ectpp.

#6

you should (re)start from an new sheet to make sure what you test :confused:
False positives and false negatives - Wikipedia

image
image732×178 18.4 KB

Documentation/Calc Functions/ISNUMBER - The Document Foundation Wiki

yep. a bit destabilizing.
I guess there should be a good reason for that in ODF :neutral_face:

actually =AL21="" is also TRUE,
but =0="" is FALSE :face_with_thermometer:

#11

Well, if you take a look at the reply that @mikekaganski posted, then it turns out your cocky know-it-all bad attitude to a genuine beginners question, was just plain wrong wasn’t it?!!

Maybe you should check yourself for knowing what the hell you’re talking about, before you jump in and make sarcastic comments that are flat out incorrect mate!!

#12

Hi @fpy ,

Thanks. Yes, I added a PS below, where I mentioned that I’d tried a brand new sheet, and found the same results. Mike’s reply sums it up.

Thanks for helping out.

Regards.

CT

#15

Note that =COUNTA(<truly blank range>) returning 1 is a bug, and a regression (it gave the expected 0 at least in v.25.2.0).

#16

Hi @mikekaganski
@karolus

OK, great! Thank you. At least that clears up the fact that I wasn’t actually going potty in trying to figure this out.

Much appreciated.

CT

#17

oops … so it is a bug: 165821 – COUNTA on a true »empty range« returns 1 (at least on my machine)

#2

PS,

I’m on Apple Mac OSX 11.7 Big Sur
LibreOffice V25.2.1.2 (X86_64)

And yes, I have Preferences > LibreOffice Calc > View > Zero values toggled to on. And I’ve flipped it several times.

And I’ve tried a brand new empty spreadsheet, and it’s still the same.

Thanks.

CT

#7

The OpenFormula standard defines why =AL21=0 returns TRUE, when AL21 is empty cell.

  1. 6.4.7 Infix Operator “=” specifies, that the two operands of the operator are of type Scalar:

Syntax: Scalar Left = Scalar Right

  1. 4.11.2 Scalar defines what the Scalar pseudo-type is, specifically that “The types Number (including a complex number), Logical, and Text are scalars”.

  2. 4.7 Empty Cell describes a distinct type Empty Cell, which is “neither zero nor the empty string”; more importantly, Empty Cell is said to not contain a value at all - see “an empty cell can be distinguished from cells containing values”, while Scalar, by definition, “has a single value”; and so, Empty Cell is not a Scalar. Therefore, when you use a value of such type in the operator taking scalars, a conversion must happen (see 6.3 Implicit Conversion Operators - 6.3.1 General).

  3. 6.3 Implicit Conversion Operators describes the rules how the implicit conversion from actual operand types happens. Namely, 6.3.2 Conversion to Scalar specifies, that to convert a reference to a single cell to a scalar, the value of that cell must be taken. But since the cell content is neither number, nor logical, nor text, then the Empty Cell must be now converted to one of these types. The conversion chooses the type of the other operand; since in your case, you compare to a number 0, the Empty Cell is converted to Number.

  4. 6.3.5 Conversion to Number specifies:

If the cell is empty (blank), use 0 (zero) as the value.

The same logic applies, if you would compare your empty cell to a Text like "". 6.3.14 Conversion to Text explains why the value of empty cell, for the purposes of = operator, is treated as "" (empty string).

For simplicity, I omitted another layer of indirection - handling of references, which constitute an own type; this should be clear from context.

Note also, that my answer does not describe why it is so. The answer to that is basically the history of spreadsheet technology; this convention happened to be most useful, and was eventually standardized.

3 Likes
#8

Note that the same standard defines behavior for Criterion separately; it is similar, but not identical.

#9

Offtop. There are other unusual phenomena in the world of formulas. For example, for some values ​​of cells A1 and A2, the formulas:
=A1=A2
and
=A2=A1
can have different values. :slight_smile:

Strange.ods (9.2 KB)

2 Likes
#10

any LO wiki or help or guide pointer to share for interested readers ?

#13

Thanks @mikekaganski ,

Very helpful. I’ve asked my instantiation of ChatGPT to remember this information for the next time we encounter a misbehaving formula involving alledgedly “empty” cells.

Cheers.

CT

#14

Querying an empty cell to see if it contains a NULL leads to either a desired or undesired result in all programs. In CALC, don’t query for NULL, but rather for a clearly declared question.
000_LO-CALC_variables declaration_125048.ods (29.4 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.