[Solved] Referencing a blank cell gives a numeric zero
Re: [Solved] Referencing a blank cell gives a numeric zero
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.
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.
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.
Re: [Solved] Referencing a blank cell gives a numeric zero
Nope, all these numeric functions ignore text, and ="" is a text with 0 characters but essentially the same as ="dummy" with 5 characters.Phillip wrote:AVERAGE(), COUNT() etc, include =<BLANK CELL>, to exclude them you have to use =IF(ISBLANK(A3);"";A3)!!
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Referencing a blank cell gives a numeric zero
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.
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Referencing a blank cell gives a numeric zero
Yes these numeric functions ignore text, but NOT "=Blank cell", that is why you have to do "=if(ISBLANK(A3);"";A3). See attached.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.
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 164 times
Open Office 4.1.11, Windows 7 Professional 64-bit
Re: [Solved] Referencing a blank cell gives a numeric zero
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Referencing a blank cell gives a numeric zero
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.
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
Re: [Solved] Referencing a blank cell gives a numeric zero
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.
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.
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.
Re: [Solved] Referencing a blank cell gives a numeric zero
How the target cell can be "blank", if it contains a FORMULA? It is nonsense...an equally good spreadsheet program that allows "=blank cell" to give a <blank cell>
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.
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.
Re: [Solved] Referencing a blank cell gives a numeric zero
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)
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
Re: [Solved] Referencing a blank cell gives a numeric zero
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.
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.
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.