[Solved] Referencing a blank cell gives a numeric zero

Discuss the spreadsheet application

[Solved] Referencing a blank cell gives a numeric zero

Postby Phillip » Fri Mar 13, 2015 2:23 pm

Why do I get zero when referencing a blank cell? See attached "Zero from blank cell.ods".
The formula in Cn is =IF(Bn>1;"?";An).
I want to be able to distinguish between cells that contain a genuine zero (A4) and blank (A3).

Yet I get a zero in C3 when A3 has never had any value?
Setting A8 to "x", then deleting it made no difference.

Tools/Options/OpenOffice Calc/View - untick Zero values is of no use as this would hide genuine zero values.
Any ideas?
Last edited by Phillip on Sat Jun 29, 2019 4:37 am, edited 1 time in total.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Fri Mar 13, 2015 2:25 pm

Zero from blank cell.ods
(9.69 KiB) Downloaded 248 times
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby Zizi64 » Fri Mar 13, 2015 2:38 pm

It is a convention: The value of an empty cell, or an empty string is 0.

You can supress this by a formula:
Code: Select all   Expand viewCollapse view
=IF(ISBLANK(A8);"";IF(B8>1;"?";A8))


Or: you can test the cell B8 too and you can combine them with a logical operator (AND, OR)
Last edited by Zizi64 on Fri Mar 13, 2015 2:40 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; 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: 8555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Referencing a blank cell gives a numeric zero

Postby Villeroy » Fri Mar 13, 2015 2:39 pm

=IF(AND(ISNUMBER(A1);A1>1);this;that)
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: 27393
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby acknak » Fri Mar 13, 2015 2:43 pm

Phillip wrote:Why do I get zero when referencing a blank cell? ...
I want to be able to distinguish between cells that contain a genuine zero (A4) and blank (A3).
...

Because that's what Calc does, and it's following what other spreadsheets do/have done.

You can test for an empty cell with ISBLANK(cell)

You can return nothing when the cell is empty with something like ...

=IF(ISBLANK(A3);"";A3)

A formula can't "copy" an empty cell. The formula can produce an empty string as the result, but then you have a cell containing something that's empty, which is different than a cell containing nothing.

 Edit: PS: 
Oops! Too slow.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Fri Mar 13, 2015 2:55 pm

Further to the above.
Setting a cell to a dummy formula such as
A1 =IF(1=2;1;"") then c1 =if(B1>1;"?";A1) will give a blank
while directly setting
A1 to "" will give "" :crazy:
No better is:
A1 to ' gives '
A1 to a space gives a space
All of which fail the ISBLANK() function.
Oddly if A1 is blank =ISBLANK(A1) gives true
whereas =ISBLANK(C3), where C3 =A1 gives FALSE. Is this because C3 contains a function, so it is not blank, or because C3 displays a zero?
Please take this as a serious question, despite the :crazy: and :ucrazy:
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby MTP » Fri Mar 13, 2015 3:59 pm

It is because C3 contains a function. ISBLANK tests for a cell having absolutely nothing in it - no spaces, no formulas, no apostrophes to designate text content.

What is the end goal you are trying to achieve? There might be an easier function to use.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Referencing a blank cell gives a numeric zero

Postby Villeroy » Fri Mar 13, 2015 4:03 pm

1=2 is always false.
A cell having a formula is not blank.

For "historic" reasons ("compatibility" with whatever), any number compared to a blank cell evaluates the blank as zero whereas any text compared to a blank cell evaluates the blank as empty string.
With A1 beeing blank
=A1=0 =>TRUE because it is equivalent to zero when compared to any number
=A1<1 =>TRUE zero is smaller than 1
=A1>-1 =>TRUE zero is bigger than -1
=A1="" =>TRUE because it is equivalent to an empty string when compared to any text
=LEN(A1)=0 => TRUE the length of the string in A1 is zero characters
=ISBLANK(A1) =>TRUE now this one tests the data type without comparison to any given type
=TYPE(A1) =>1 another test for the data type
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: 27393
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby keme » Fri Mar 13, 2015 4:20 pm

Phillip wrote:Further to the above.
Setting a cell to a dummy formula such as
A1 =IF(1=2;1;"") then c1 =if(B1>1;"?";A1) will give a blank
while directly setting
A1 to "" will give "" :crazy:
...
This may seem odd, I agree. The ISBLANK() function only returns true if the referenced cell does not contain anything. A reference to nothing is still a reference, so it's not nothing :) This means that you can't forward emptiness. You can have a function in some other programming language return "void", but this abstraction is not available to spreadsheets.

The logic is this: ISBLANK() returns the state of a cell, not the nature of its evaluation.

As for entering an empty string in a cell, you do not need quite as elaborate constructs as what you suggest. ="" will do.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3287
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Sat Mar 14, 2015 3:13 pm

MTP asked "What is the end goal you are trying to achieve?"
Simply if col B was >1 then set col C to "?", otherwise copy col A into col C.
i.e. in C1 =IF(B1>1;"?";A1)
Further this to be an exact copy, i.e. if col A was unused (blank) then col C would also be blank.
It appears that this is impossible.

The best workaround I can see is:
Preset col A to ="" (an empty string) (thanks Keme), then col C will show nothing.
setting col C to =IF(ISBLANK(A1);"";IF(B1>1;"?";A1)) does work, but seems dreadfully clumsy.

Am I right there is no way of getting a function to give a <BLANK> result?
If C3=IF(ISBLANK(A3);"";A3), then =ISBLANK(C3) gives FALSE.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby Villeroy » Sat Mar 14, 2015 4:15 pm

Well, if you think, a nested IF is "dreadfully clumsy" then you should not use spreadsheets.

May be my first suggestion meets your aesthetic sentiment? =IF(AND(ISNUMBER(B1);B1>1);B1;"?") [if both conditions isnumber(B1) AND B1>1 are true, then return B1, else "?"
Instead of turning numbers into text you could also apply a user-defined number format to B1 such as [>=1]General;[<1]"?";General
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: 27393
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby RusselB » Sat Mar 14, 2015 5:47 pm

Am I right there is no way of getting a function to give a <BLANK> result?
In Calc this is correct. I have used other spreadsheets where it is possible to have it return blank, but they are much older (DOS based), and therefore do not have many capabilities Windows users are used to/expect
OpenOffice 4.1.7 and LibreOffice 6.3.3.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: 5702
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Sun Mar 15, 2015 8:43 pm

Thanks RussellB, I accept Calc has this limitation.
I was thinking of DB2 and its "null" function/value. Although I accept this also had its complications!

Villeroy, thanks for your suggestions:
Perhaps I was not clear: I wanted "?" in cell C1 when cell B1 was > 1, to prompt the user to enter an amount,
otherwise a straight copy of what was in A1 - showing exactly what was displayed, i.e. blank for blank or zero / non-zero number.
I tried =IF(AND(ISNUMBER(B1);B1>1);B1;"?") - I assume you meant C1=IF(AND(ISNUMBER(A1);B1>1);A1;"?")
However this gives "?" when A1 is blank, and B1=2 since ISNUMBER(A1) gives FALSE (see below)

Perhaps I should have said "ineligant" instead of "clumsy".
I do not mind using complicated IF's when I need to. The same spreadsheet contains
=T(STYLE("Default"))&IF(AND(F13>0;ISNUMBER(E12);E12<>H12);T(STYLE("Red"))&"Check split";"")

What does work is:
C1=IF(ISBLANK(A1);"";IF(B1>1;"?";A1))
C1=IF(B1>1;"?";IF(ISBLANK(A1);"";A1))
or
C1=IF(B1>1;"?";A1) if I preset all unused An to ="".

Curiously: I find:
A1: blank =IF(A1=0) TRUE
A2: =A1 =IF(A1=0) TRUE

A1: blank ISBLANK(A1) TRUE
A2: =A2 ISBLANK(A2) FALSE

A1: blank ISNUMBER(A1) FALSE
A2: =A1 ISNUMBER(A2) FALSE
yet in all cases =A2 displays zero!

Well at least I now know: If I see a zero, to check if it is a zero and not a blank use ISNUMBER, not ISBLANK. (Excuse the humour).
Seriously - I accept fully that spreadsheets have to evaluate blank cells as zero value, empty strings etc.
However I think where the result of a function is a blank cell and not number zero, it should display blank and ISBLANK() should give TRUE.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Mon Mar 23, 2015 3:19 pm

Further to the above:
keme suggested ="" as a solution, which matches Zizi64 saying the value of an empty string is zero.

However this is not what I find:
Set A1 ="", B1 =1, then =A1+B1 gives 1 - ok
But =IF(A1>1) gives TRUE, so it appears Calc interprets an empty string as both zero and greater than 1.

It seems to me that there is definitely a bug!
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby acknak » Mon Mar 23, 2015 4:08 pm

Phillip wrote:... it appears Calc interprets an empty string as both zero and greater than 1.

It seems to me that there is definitely a bug!

You're conflating two different operations. When doing arithmetic, the empty string, or empty cell, are converted to numeric zero. When comparing values, any string is greater than any number, so "" > 0 is TRUE.

It's not a bug, it's just a crusty old design. Spreadsheets are a virtual "swiss army" knife: not pretty but useful nonetheless.

Looking for consistency and logic in spreadsheets is a recipe for a nervous breakdown.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Referencing a blank cell gives a numeric zero

Postby keme » Tue Mar 24, 2015 10:49 am

acknak wrote:...
Looking for consistency and logic in spreadsheets is a recipe for a nervous breakdown.
+1!
Adding some spice to this recipe for madness:
  • Calc does not perform strong type checking, which allows for comparing sting vs number, which requires some "consistent" result of such a compare. This "loose typing" also comes into play for boolean values. With any logical operation, TRUE returns as the number 1, and FALSE as the number 0. However, any nonzero numerical value supplied to a logical function is interpreted as TRUE.
  • Calculations which should return zero, sometimes don't because of rounding error. Testing for zero easily fails...
  • Calculations by functions will not always be consistent with calculations by operators. (Text interpreted by operators, disregarded by functions)
The bottom line:
Make sure you know the strengths and the limitations of your tools.
Attachments
inconsistent.ods
(9.43 KiB) Downloaded 87 times
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3287
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Referencing a blank cell gives a numeric zero

Postby Villeroy » Tue Mar 24, 2015 12:29 pm

If Calc were more consistent, millions of existing spreadsheet documents would break. We alread have complaints that Calc does not calculate certain "numbers" where Excel happily treats ambiguous numerals as numbers ("2/3/2015" vs. "3/2/2015" and "123.456" vs."123,456"). As a compromise, Calc performs these tricks only with unambiguous ISO-dates (yyyy-mm-dd) or integers (digits only).

-- I strictly avoid numeric text if the values are supposed to be numbers. Numeric text is good for identifiers (zip codes, part numbers, phone numbers etc). Doing arithmetics with identifiers makes no sense but leading/trailing zeroes may make a huge difference. The part number "0123.0140" should be taken literally as a sequence of 8 digits with a dot in the middle. Apart from identifiers all numeric text is evil.
-- I avoid the "traditional conditional" such as =IF(X1="" ; Blah/X1 ; ""). If a missing value raises an error then I rarely see any reason why I should hide away that error, particularly when this "solution" generates data fields with mixed numbers and text. Either a data set is complete or it should be deleted all together. This is one point where databases enforce a higher level of consistentency by design while spreadsheet users keep on struggling with esoteric work-arounds.
-- When calculating balances (test if resultA equals resultB), I use rounding because floating point operations may lead to tiny rounding errors where 2 equal looking figures are not exactly equivalent. Another point where databases can do more with less effort.
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: 27393
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Wed Jul 11, 2018 10:32 pm

I realize this is an old thread, and apologies for resurrecting it, but I'm wrestling with zero/blank issues with calc now and thought I'd share my experience with the collective. I've stopped by this forum a few times in the past and I appreciate everyone's help.

Even though I try to be careful to avoid having to test if the content of a cell is "blank", I do find it necessary to do that from time to time. I've turned on "display zero values" in Tools>Options>OpenOffice Calc>View to spot as many zeros that look like blanks as possible and process them accordingly. This works up to a point.

I thought I developed a broader solution today. I used "substitute" to remove "spaces" from cells, because they look like blanks and my user could enter a space into a cell by mistake and I have to be able to recognize it as a blank. Once I do that, I reason that a cell, A1, is blank if its value is zero, or if its len is zero. Otherwise, it must contain useful text for me to process.

When I tried this approach, it failed. I encountered a situation where a cell, say A1, had a zero in it (I could see it), but if(A1=0) was FALSE, len(A1)=1, isnumber(A1)=FALSE, and istext(A1)=FALSE. Does that make sense, or is it just part of the "recipe for madness"? :lol: I've worked around this problem by noting that none of my text data in this particular column consists of fewer than about six or seven characters, so rather than use len(A1)=0 to detect a blank, I'm using len(A1)<=1. This will eventually bite me, because some of my columns contain one-character text "codes".

If anyone has figured out a way around this, I'd like to know about it. And, thank you in advance for not suggesting that I convert this application to a database 8-) . I've plowed that ground extensively already.
OpenOffice 4.1.3 on Windows 7
FrogFan
 
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: Referencing a blank cell gives a numeric zero

Postby FJCC » Wed Jul 11, 2018 10:42 pm

Can you post a file showing the behavior you describe? I can't reproduce it.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7396
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Referencing a blank cell gives a numeric zero

Postby Lupp » Wed Jul 11, 2018 11:20 pm

1.
To detect a REAL blank :NO CONTENT AT ALL - NEITHER NUMBER NOR TEXT NOR ANY FORMULA: in A1 use
Code: Select all   Expand viewCollapse view
=ISBLANK(A1)

2.
=(A1="") and =(A1=0") both return TRUE for a blank cell despite the fact that =(""=0) returns FALSE. This is not only nonsense in itself, but also a violation of the very fundamental "transitivity of the equality relation". Spreadsheets worry about compatibility with Excel. They don't worry about logic or mathematics.
In fact a blank cell A1 should answer FALSE on both the questions at the start of '2.' because it answers FALSE on =ISNUMBER(A1) and on =ISTEXT(A1) as well, and 0 is a number and "" ist the empty text.
3.
On te other hand there are situations where you would neeed then a comdition like OR(ISBLANK(A1);A1=""), and thats annoying.
You may feel there is missing a clear concept. I feel so.
4.
There is a next to infinite number of ways to explicitly lie with the help of conditional formats, whether consciously or inadvertently.
You see: No chance to catch your issue without having your document at hand.
5.
Nevertheless we manage to get along with spreadsheets, and sometimes they are even useful. Keep them simple to get a better chance.

Also see attachment.
Attachments
aoo75950MessingUpDecisions_1.ods
(15.6 KiB) Downloaded 65 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2570
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Thu Jul 12, 2018 12:10 am

I must admit I had forgotten this thread, perhaps I should have closed it!
Lupp, your clever formatting to display a zero in A1 is ingenious, but it does not reproduce what FrogFan reported since ISTEXT(A1) gives TRUE.
So FrogFan, please post a file showing an example.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Thu Jul 12, 2018 2:59 am

Thank you for your assistance. I've attached a workbook for your review. When you open it, you don't need to allow the macro, because I don't use it anymore. Don't update the links, because you don't have the file it links to. Open the "Members Detail" tab and scan down to row 4. Look at columns G and then AG-AJ. AG-AJ is where the following functions are applied to G4, in this order: isblank(G4), len(G4), isnumber(G4) and istext(G4). You should see the behavior I described.

This workbook is used together with others for different months, to track attendance at meetings for a certain women's club. The current month workbook has links to the previous months, and there are links between sheets in the current month. The bad behavior seems to come from the links, although the linked spreadsheets have basically the same data for different months.

Thanks again for any help.

TestNov 2018 Sign-In Sheet.ods
(43.62 KiB) Downloaded 69 times
OpenOffice 4.1.3 on Windows 7
FrogFan
 
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: Referencing a blank cell gives a numeric zero

Postby FJCC » Thu Jul 12, 2018 3:44 am

In the current state of the document, G4 effectively contains the formula
Code: Select all   Expand viewCollapse view
=AC4

When AC4 is blank, you hit a curious case for the performance of ISTEXT() and ISNUMBER(). The situation is a little clearer if you put the formula
Code: Select all   Expand viewCollapse view
=ISFORMULA()

in AK4. When AC4 is blank, ISTEXT(G4) and ISNUMBER(G4) return FALSE but ISFORMULA(G4) is TRUE. If you put text into AC4, ISTEXT(G4) becomes TRUE and if you put a number in AC4 ISNUMBER(G4) becomes TRUE. In all cases, ISFORMULA() remains TRUE. Why is a zero displayed in G4 when AC4 is blank? I don't know.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7396
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Thu Jul 12, 2018 4:48 am

Thank you, FJCC. I'm wondering now how I can use the fact that ISFORMULA() returns TRUE (I never thought to test this) together with the other tests to ascertain whether I'm dealing with a "blank". The cell we're looking at here (G4) needs to be regarded as a blank.

I think I'll scratch my head about this awhile. In the meantime, since I'm working with names, I'll never get one with LEN() less than about 5 or 6, so my workaround looking for LEN() <= 1 should stand up.
OpenOffice 4.1.3 on Windows 7
FrogFan
 
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: Referencing a blank cell gives a numeric zero

Postby MrProgrammer » Sat Jul 14, 2018 9:21 pm

FrogFan wrote:I encountered a situation where a cell, say A1, had a zero in it (I could see it), but if(A1=0) was FALSE,
I am unable to see where your attachment demonstrates that the test A1=0 is FALSE. In my testing if A1 is empty, =A1=0 is TRUE.

FrogFan wrote:The cell we're looking at here (G4) needs to be regarded as a blank.
=NOT(OR(ISNUMBER(G4);ISTEXT(G4);ISERROR(G4)))
A non-empty cell contains a number, text, or an error condition. If none of these apply, it's empty. If you have option Precision as shown turned off, ISNUMBER of an empty cell is FALSE and you can use the formula above to detect an empty cell. However, if Precision as shown is turned on, this action can change the values in cells and G4 will have the value 0 and is not empty even though AC4 is empty.

FJCC wrote:Why is a zero displayed in G4 when AC4 is blank?
Consider this suggestion. Spreadsheets were originally developed to do calculations with numbers, so =AC4 when AC4 is empty, asks for the numeric value of the data in AC4, which is zero. The real answer is that Calc is just mimicing the behavior of earlier spreadsheets.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3902
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Sun Jul 15, 2018 12:20 am

I think what is being missed is the inconsistency of CALC (This may apply to other spreadsheet programs).
While a copy of an unused cell sometimes behaves like an unused cell:
ISTEXT() ISNUMBER() both FALSE, =zero, ="" both TRUE
Yet an unused cell is ignored for functions such as AVERAGE() and COUNT(), yet the copy is not!
Since CALC must know how to handle an unused cell, i.e. some sort of NULL vaue, it would seem to me a simple matter IMHO, for a formula to return that same NULL value, both for display and further calculations.
I accept that all existing spreadsheets using AVERAGE, COUNT etc might suddenly give different values, but these would, for once, be correct!
Should there be a genuine concern that existing spreadsheets might not work - Can I suggest a new option in CALC of "Treat a copy of an unused cell as unused (blank)".
Though I suspect anyone seeing such an option might say "Why would I ever want anything else!"
Last edited by Phillip on Sun Jul 15, 2018 1:14 am, edited 1 time in total.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Sun Jul 15, 2018 12:43 am

=NOT(OR(ISNUMBER(G4);ISTEXT(G4);ISERROR(G4)))
A non-empty cell contains a number, text, or an error condition. If none of these apply, it's empty. If you have option Precision as shown turned off, ISNUMBER of an empty cell is FALSE and you can use the formula above to detect an empty cell. However, if Precision as shown is turned on, this action can change the values in cells and G4 will have the value 0 and is not empty even though AC4 is empty.


I think I'll try this approach. One thing I don't get, though, is that, for this example, ISFORMULA(G4) = TRUE, which seems to indicate that the cell is not empty. If you say it doesn't matter if ISFORMULA() is TRUE as long as all the others above are FALSE, I accept that, I just don't understand it. But maybe that's asking too much! :D

Thanks for the help.
OpenOffice 4.1.3 on Windows 7
FrogFan
 
Posts: 27
Joined: Tue Aug 30, 2016 6:51 pm

Re: Referencing a blank cell gives a numeric zero

Postby MrProgrammer » Sun Jul 15, 2018 7:54 pm

FrogFan wrote:One thing I don't get, though, is that, for this example, ISFORMULA(G4) = TRUE, which seems to indicate that the cell is not empty.
Of course the cell is not empty. G4 contains the formula =IF(LEN(I4)>1;I4;AC4).

FrogFan wrote:If you say it doesn't matter if ISFORMULA() is TRUE as long as all the others above are FALSE, I accept that, I just don't understand it.
I should have said: A cell may hold:
• A number, like 25
• Text, like "Hello world"
• An error condition, like #N/A
• A formula which returns one of the former
• A formula which the zero-length string, ""
• A formula which references an empty cell
• Nothing (none of the above)
Test for the latter using =ISBLANK(cell). Test for formulas with =ISFORMULA(cell). Test for any of the first five with =NOT(OR(ISNUMBER(cell);ISTEXT(cell);ISERROR(cell))).
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3902
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Sun Jul 15, 2018 8:58 pm

Ah but all that complication
=NOT(OR(ISNUMBER(cell);ISTEXT(cell);ISERROR(cell)))
would be unnecessary if, when the result of a formula is a blank cell, then the cell value (for processing and display) is also blank!
Then =ISBLANK(), AVERAGE(), COUNT() and no doubt many more functions would work fine!
As it is they don't and everyone has to work hard to code around this eccentricity.
Yes, I know others will say "That is how spreadsheets work" & "You cannot expect a spreadsheet to follow the rule of basic logic".
Ho hum, I suppose we should be grateful that "=1+1" gives 2.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 81
Joined: Wed Jan 09, 2013 1:50 pm

Re: Referencing a blank cell gives a numeric zero

Postby Zizi64 » Sun Jul 15, 2018 9:14 pm

...when the result of a formula is a blank cell


A cell is not "blank" when it CONTAINS a formula. Maybe the result is an EMPTY STRING: "", and you can not see anything in the cell, but that cell is not empty.

The BLANK an the VISUALLY BLANK states of the cell are not equal.

You can use the ="" condition for the empty string results.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; 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: 8555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Next

Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests