[Solved] Referencing a blank cell gives a numeric zero

Discuss the spreadsheet application

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

Postby RusselB » Sat Jun 29, 2019 4:48 am

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 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5486
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby Villeroy » Sat Jun 29, 2019 3:58 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby keme » Sun Jun 30, 2019 11:20 am

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
keme
Volunteer
 
Posts: 3255
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Villeroy » Mon Jul 01, 2019 12:46 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Phillip » Tue Jul 02, 2019 3:07 pm

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 6 times
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

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

Postby Villeroy » Tue Jul 02, 2019 3:21 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27200
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Phillip » Tue Jul 02, 2019 9:13 pm

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.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

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

Postby RusselB » Tue Jul 02, 2019 9:55 pm

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 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5486
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby Zizi64 » Wed Jul 03, 2019 6:36 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8319
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Phillip » Mon Jul 08, 2019 10:57 am

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 and 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 '=""(4), 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 then it is NOT a real zero (and so is ignored by COUNT(), AVAERAGE() etc yet is still processed as zero or a string as before)
Either setting a cell to a string '="" (CALC knows it is NOT a real zero - as now)
Or a copy of an unused cell - "No numeric value has ever been set" (CALC decides 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!
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

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

Postby RusselB » Mon Jul 08, 2019 2:33 pm

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 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5486
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Previous

Return to Calc

Who is online

Users browsing this forum: Google [Bot], jeffs12 and 25 guests