Page 3 of 3

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

Posted: Sat Jun 29, 2019 4:48 am
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.

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

Posted: Sat Jun 29, 2019 3:58 pm
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.

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

Posted: Sun Jun 30, 2019 11:20 am
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.

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

Posted: Mon Jul 01, 2019 12:46 pm
by Villeroy
Spreadsheet formula language is not the only value system doing weird implicit conversions: https://devrant.com/rants/940938/christ ... javascript

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

Posted: Tue Jul 02, 2019 3:07 pm
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.

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

Posted: Tue Jul 02, 2019 3:21 pm
by Villeroy
Ah, sorry I forgot about that turn. Yes, you are right. Spreadsheets are awful.

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

Posted: Tue Jul 02, 2019 9:13 pm
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.

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

Posted: Tue Jul 02, 2019 9:55 pm
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.

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

Posted: Wed Jul 03, 2019 6:36 am
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.

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

Posted: Mon Jul 08, 2019 10:57 am
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)

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

Posted: Mon Jul 08, 2019 2:33 pm
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.