[Solved] Referencing a blank cell gives a numeric zero

Discuss the spreadsheet application
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by RusselB »

Currently, to my knowledge, there is no spreadsheet software that will return a blank cell.
This makes sense to me. as a cell can be blank (nothing in it), have text, have a number or have a formula.
If you have a formula in a cell that returns a blank cell, what does that cell contain? Is it blank (nothing in it) or does it have a formula.
Logically, to my mind at least, it contains a formula.

Formulas can (and have been) written so that if the result is 0, the cell appears blank. As already mentioned earlier, one could change the setting to suppress the display of 0's...however, there are times when I want the 0 to be displayed in one formula, but not in another. Changing the setting removes my ability to control which 0's are displayed and which one's are not.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Villeroy »

Phillip wrote:AVERAGE(), COUNT() etc, include =<BLANK CELL>, to exclude them you have to use =IF(ISBLANK(A3);"";A3)!!
Nope, all these numeric functions ignore text, and ="" is a text with 0 characters but essentially the same as ="dummy" with 5 characters.
COUNTA is the only exception. It counts both, numbers and text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
keme
Volunteer
Posts: 3692
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by keme »

Calc is intended as a "programming tool for non-programmers", aididng with calculations primarily. Thus, the default interpretation of an empty cell is the number zero.

Introducing the distinction between NULL (no value at all) and zero (a value with magnitude 0) is an abstraction which will throw off more users than it will help. Users who need to make that distinction should also understand that it cosntitutes an additional level of abstraction, and learn to handle it (e.g. as suggested, by detecting an empty cell and returning empty string values when source cell is empty).

Other programming languages have inherent distinction between NULL and zero. They also, generally, have a higher entry threshold for users. You have a choice. It is always good to know the strengths and weaknesses of the tool you use, at any level of advancement/abstraction.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Villeroy »

Spreadsheet formula language is not the only value system doing weird implicit conversions: https://devrant.com/rants/940938/christ ... javascript
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Phillip
Posts: 102
Joined: Wed Jan 09, 2013 1:50 pm

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Phillip »

Villeroy wrote:Nope, all these numeric functions ignore text, and ="" is a text with 0 characters but essentially the same as ="dummy" with 5 characters.
Yes these numeric functions ignore text, but NOT "=Blank cell", that is why you have to do "=if(ISBLANK(A3);"";A3). See attached.
The "Sales by salesman" show why there is a non-trivial difference between a blank cell (not in Count/Average) and a cell displaying zero.
A reader might assume "blank" means the same as "did not make any sales" See attached, cell B16.
Attachments
To get Count and Average to work.ods
(14.39 KiB) Downloaded 132 times
Open Office 4.1.11, Windows 7 Professional 64-bit
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Villeroy »

Ah, sorry I forgot about that turn. Yes, you are right. Spreadsheets are awful.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Phillip
Posts: 102
Joined: Wed Jan 09, 2013 1:50 pm

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Phillip »

Thanks Villeroy -also thanks for all your previous help on my other posts.
Actually I find OO Calc very useful. Excepting this point of course, though I do fully understand why.
Should you ever hear of an equally good spreadsheet program that allows "=blank cell" to give a <blank cell> value let me know - though it would still have to have the easy to use toolbars as CALC - not the new-fangled incomprehensive banners that Microsoft now give you! NB, That is why I have not switched to Libre Office.
Open Office 4.1.11, Windows 7 Professional 64-bit
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by RusselB »

Quattro Pro, when it was part of Borland, had the ability to report a blank cell when referencing a blank cell.
Quattro Pro is now part of WordPerfect ant that, amongst many other features that existed under Borland, have now been removed.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Zizi64 »

an equally good spreadsheet program that allows "=blank cell" to give a <blank cell>
How the target cell can be "blank", if it contains a FORMULA? It is nonsense...

Every cell of a spreadsheet can contain three type of the contents (even in same time!):
- a numeric value (maybe it is a constant or a result of a formula; the numeric value is 0 when the cell contains a real string without numeric value.)
- a string (maybe it is a string constant, or a formatted appearance of the referenced numeric value containing by the cell),
- a formula

This behavior is same in the all of versions of the Excel, in all of versions of the OpenOffice, Neo Office, LibreOffice.

A really blank cell have not contain anything: neither a string, nor a number, nor a formula.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Phillip
Posts: 102
Joined: Wed Jan 09, 2013 1:50 pm

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by Phillip »

I completely accept that Zizi64 is right. At the same time a cell is interpreted as a numeric, value AND a string, which can either be the default, entered values, or the result of a formula.
I also accept this is how spreadsheets work.
The reason I raised this post is to report the inconsistency in processing and displaying.
(To save viewers looking at what has been said before in this post):
1: An unused cell behaves as: An empty string, numeric zero for calculations gives TRUE for ISBLANK(), and IS ignored by numeric functions like COUNT(), AVERAGE() etc.
2: '=(unused cell) behaves as: An empty string, numeric zero for calculations gives FALSE for ISBLANK(), and is NOT ignored by numeric functions like COUNT(), AVERAGE() etc.
3: '="" behaves as: An empty string, numeric zero for calculations gives FALSE for ISBLANK(), and IS ignored by numeric functions like COUNT(), AVERAGE() etc.
4: '=0 (A real zero) behaves as: An empty string, numeric zero for calculations gives FALSE for ISBLANK(), and is NOT ignored by numeric functions like COUNT(), AVERAGE() etc.
(1) and (3) display nothing, (2) and (4) display a zero (Unless you switch this off via Tools>Options>Open Office Calc> View, in which case BOTH display nothing, so no help at all!!.
Using ISBLANK() to distinguish between (1) - TRUE and the rest - FALSE - no problem.

It still seems to me that since CALC can work out the difference for '=""(3), why not for '=unused cell(2)
or in other words
- if a formula results in a numeric value ( 1+2-3) then it is a real zero. (As now)
- if a formula results in a non-numeric value (As now) - or a copy of an unused cell (Not as now)
then it is NOT a real zero (and so is ignored by COUNT(), AVERAGE() etc yet is still processed as zero or a string as before)
As it is: Setting a cell to a string '="" (CALC knows it is NOT a real zero - as now)
Setting a cell to an unused cell - "No numeric value has ever been set" (CALC decides it IS a real zero) ?????.

Finally - I accept that this is how all spreadsheets work, and we will have to use formulae such as '=IF(OR(ISBLANK(A1);NOT(ISNUMBER(A1);"";A1)...
And also thank Zizi64 for all his help on this and many other posts.
Oh if you do hear of a spreadsheet program that can see the difference between real zeros and ... do let me know!
(Minor edits made on 10 Jan 21 to make more clear)
Last edited by Phillip on Sun Jan 10, 2021 10:08 pm, edited 1 time in total.
Open Office 4.1.11, Windows 7 Professional 64-bit
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Referencing a blank cell gives a numeric zero

Post by RusselB »

I only know of one spreadsheet that used to be able to distinguish between the difference of a cell having a 0 calculation value and a 0 from a null entry.
That was Borland Quattro Pro. Quattro Pro is now owned by WordPerfect and, last I saw, had this and other features that seemed to make it unique, removed.
If you can find a copy of Borland Quattro Pro v 5.x, that is the last version that I'm awate of that had the ability to make that distinction.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply