Page 2 of 3

Re: Referencing a blank cell gives a numeric zero

PostPosted: Sun Jul 15, 2018 11:42 pm
by FrogFan
I appreciate everyone's patience and assistance. I understand a bit better now than I did a couple days ago, but there are still inconsistencies I need to watch for, and that will cause me problems later if I'm not careful. For example, I just noticed that =G4="" returns TRUE while LEN(G4)=1.

Edited to correct typo.

Thanks again.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Mon Jul 16, 2018 1:13 pm
by keme
Spreadsheets are, primarily, tools for calculation. In certain situations when you ask for a result (by entering a formula) and the data type of the result is not implied (by functions, operators or preexisting data), it will assume that the desired result is numeric.

This is not so different from what you get when you switch on your pocket calculator. Even advanced programmable models with graphing, text, matrix and complex number operations will assume that your starting point is the real number zero.

Arguing this design point in a user forum is rather useless. It is the way of spreadsheets. It may change in the future, but I would not hold my breath in anticipation. Live with it and utolize the workarounds.

If you can't live with it until the change comes, find a different tool. Several modern programming languages have the option to return the "void" data type in various contexts. The closest you get to the spreadsheet paradigm with handling of references to nothingness/void/empty may be Haskell.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Mon Jul 16, 2018 6:24 pm
by Villeroy
FrogFan wrote:I just noticed that =G4="" returns TRUE while LEN(G4)=1.

What is in G4 actually? I can't reproduce this. G4="" implies LEN(G4)=0

Re: Referencing a blank cell gives a numeric zero

PostPosted: Mon Jul 16, 2018 6:47 pm
by MrProgrammer
Villeroy wrote:What is in G4 actually? I can't reproduce this. G4="" implies LEN(G4)=0
G4 is a formula reference to an empty cell. Therefore an equality text with G4 and the null string has the value TRUE. However, since Calc treats the reference as numeric, G4 shows 0, and LEN(G4) is 1! An equality test with G4 and zero is also TRUE even though ""≠0.

I believe the "length" of a numeric value is not clearly specified in the Calc documentation. For example, if D5 contains =1/3 and is formatted with format code 0.00 what is LEN(D5)? Even people who are fairly experienced with Calc will probably have to run a test to determine the answer. And having run that text, can the person then say, without testing, what happens with =1/12 in D5?

Or, if I enter -1 into cell P4 with format code General, it displays as -1 and LEN(P4) is 2 but if I enter -0 it displays as -0 but LEN(P4) is 1! Who would have known that? My opinion is that it is a mistake to use the LEN function unless the data is text. As a guess, if a cell is numeriic, =LEN(cell) is =LEN(TEXT(cell;"General")) where one must replace General with the corresponding Format Code in the locale.

I use =IF(ISNUMBER(cell);cell;T(cell)) to copy the value of a cell when the contents are not known. When the source cell is empty, the formula displays as empty.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Wed Jul 18, 2018 1:31 pm
by Phillip
It would appear that the problem has not been understood:
I am referring to the two different properties of a cell: What it contains vs how it is processed. See attached example.
CONTENT:
A cell contains nothing (if unused), a value, or a formula that results in a value (or an error, but as this usually is corrected I will ignore this option).
PROCESSING.
An unused cell is considered blank,neither a number nor text, yet if used in a formula is processed as either zero or an empty string for simple arithmetic.
However it is ignored for functions such as AVERAGE, COUNT - presumably because it is recognised as not actually being a number.

HOWEVER if a cell is a copy of an unsused cell, then:
First a zero is displayed.
It is processed as an actual number by functions AVERAGE & COUNT.
YET is still considered the same as an unused cell !!! - Neither a number nor text, AND as either zero or an empty string!
It would seem to me that this contradiction is a bug - although I am always open to a sensible explanation.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Wed Jul 18, 2018 1:59 pm
by Zizi64
Note Cell A2 is unused, C2 is a direct copy but seemingly processed as number zero.


The formula =A2 means (by default: = "numeric value of the content of the cell"(A2). That equals 0, therefore the AVERAGE function will use the zero value located in the C2 for the calculation. There is not ANY difference between the results of the formulas in cells A2 and A3.

You need use the next formula - when you want eliminate the result referenced from a really empty cell:

Code: Select all   Expand viewCollapse view
=IF(ISBLANK(A2);"";A2)

or - if the cell content IS a "string or number":
Code: Select all   Expand viewCollapse view
=IF(ISNUMBER(A2);A2;"")

The "empty"/"nothing"results of these formulas will not be calculated in the average.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Wed Jul 18, 2018 2:05 pm
by Villeroy
From the view point of the application programming interface, every cell has a numeric value, a string, a formula and an error at the same time.
A blank cell has value 0, string "", formula "" and error 0.
When you compare it with a string, e.g. =A1="", the result is TRUE (1) because the string is "".
When you compare it with a number, e.g. =A1=0, the result is TRUE (1) because the value is 0.

=ISFORMULA(A1) returns FALSE and =FORMULA(A1) returns an error if A1 is a constant. Nevertheless there is a formula from the API point of view. The formula of a blank cell is an empty string, for any constant text the formula string is that literal text, it is 123.45 for a number, '123.45 (with a leading apostrophe) for a numeric string and '=123.45 (with a leading apostrophe before the leading =) for a literal string starting with an equal sign, even though none of the examples involves any calculaion.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Wed Jul 18, 2018 3:12 pm
by keme
If you explicitly reference a cell in your formula, you tell the software to evaluate your formula based on the value that can be extracted from that cell. For most people, an empty cell intuitively implies one of the nil values indicated by Villeroy, data type depending on the context assumed by your formula. If the formula does not force a particular context for the reference, numeric nil - zero - is assumed.

Returning a value of nothingness/null/void for an empty cell is perhaps tecnically more precise, but the handling of that return is not as intuitive to the common user. Hence, nil was preferred over null when spreadsheets came to be. This is intended behavior, not a bug.

It may be a flaw in the software for some uses, but the spreadsheet is for quick and easy data modeling, rather than robustness and integrity. You can work around it if you need it and you must use a spreadsheet.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 12:47 am
by Phillip
Yes I see these arguments, but I still think that =cell should give an exact copy of what was the value in the cell not amend it to a "nil". Otherwise why doesn't =cell copy over formulae?

Indeed I would have thought the "common user" would have understood (and preferred) that any formula resulting in =(blank cell), also gave a blank cell for number and string values. Any further use of that cell in later formulae would still work as it would still be processed as zero or an empty string - as indeed as CALC does at present since the other tests (ISNUMBER, ISTEXT) give the same result - FALSE, despite the display of a zero!

For =(blank cell) to give something else - a curious "nil" which is then processed as the number zero, with a LEN of 1, with ="" still being TRUE seems to me anything but "intuitive".
Particularly when a string of a single space gives a LEN of 1, with ="" being FALSE!

Please note that I can understand that this was the design decision of many years ago for a "quick and dirty (sorry - easy) solution" when all that was wanted was simple arithmetical calculations, but it is anything but easy for modern data analysis as the workaround =NOT(OR(ISNUMBER(G4);ISTEXT(G4);ISERROR(G4))) shows.

Is my suggestion of an option so that =G4 directly copies a blank cell, where ISBLANK is TRUE for values, even if ISFORMULA is TRUE, just a pipe-dream for a logical future?

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 12:51 am
by Villeroy
Phillip wrote: but it is anything but easy for modern data analysis as the workaround

Spreadsheets are not modern by any means.
What's wrong with =ISBLANK(G4) :?:

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 7:08 am
by Zizi64
Yes I see these arguments, but I still think that =cell should give an exact copy of what was the value in the cell not amend it to a "nil". Otherwise why doesn't =cell copy over formulae?


How a formula would be "copy" the empty state of a source cell, when a formula IS LOCATED in the target cell? The target cell never will be really empty, because it contains a formula.

Use the ISBLANK() function and the IF() statement for this task, as I wrote the samples above.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 2:13 pm
by Phillip
Ok, I accept defeat! Calc does not allow a blank value to be assigned to a cell.
I use a similar answer to what Zizi64 suggested =IF($K548=AR$8;$H548-$E548;"") (If category of spending matches value in heading, value is Credit-Debit, else empty string).
My first thought was =IF($K548=AR$8;$H548-$E548;BLANK()) but I guess too advanced for a spreadsheet. :D .
Seriously, thanks for the help, all of you.
OK if I now close the topic?

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 3:55 pm
by FrogFan
From my perspective, I think I've learned as much as I can from the experts here, but I thought I'd post one more item about my attendance spreadsheet in case others have a similar problem.

My problems begin when I link a cell in, say, October's sheet to a cell in September's sheet. I do that by using placing "=cell reference from September sheet" into October's sheet. Then, what was blank in September is no longer blank in October. ISBLANK(cell) is FALSE in October, but it was TRUE in September. I ended up looking at LEN() or testing for "=0" to try to figure out if the cell is "really" blank. Then I noticed that even if ISBLANK() is FALSE, ="" is TRUE in some cases -- I'm not sure I know exactly when this happens -- so I'm able use that test for a blank. I think I've been able to capture all the cases that matter in my spreadsheet, and I'm able to find "blanks" when I need to.

In the future, I'm going to try to avoid having any blanks when working with text data. I'll assign a value of "zz" or some other code in that case and use that value as a "blank". Then I'll know for sure what I'm dealing with.

I too appreciate everyone's help here. I've learned a lot about how calc works. Thank you.

As far as I'm concerned, you can close this topic.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 4:13 pm
by Villeroy
FrogFan wrote:My problems begin when I link a cell in, say, October's sheet to a cell in September's sheet.

Right. Because you split your data in a sheet for September and another sheet for October. This is a plain wrong approach.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 5:53 pm
by FrogFan
Right. Because you split your data in a sheet for September and another sheet for October. This is a plain wrong approach.


What's a right approach? Bear in mind that each month's spreadsheet contains 8 tabs to keep track of various things. Are you saying to put all the months in the same spreadsheet, resulting around 100 or so tabs? But even if I do that, as soon as I set the value of a cell equal to the value of another, ISBLANK is no longer true. Separate records are required of attendance, by member, by month. Or, perhaps are you saying that I ought to have columns in a single spreadsheet for relevant monthly data? In that case, I will have a spreadsheet to maintain with 32*12=384 columns.

I've looked into building a database application for this, but it's not worth it. There is a lot of complexity related to keeping track of membership status, who pays for lunch, who doesn't show when they're supposed to, how many guests are attending and who invited them, etc. Doing this in a database application isn't trivial. Moreover, what I have now "mostly" works.

I look forward to your response.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 6:47 pm
by Villeroy
Do not use a spreadsheet when you need to a database actually.
If you do use a spreadsheet as a database anyway, then use it in similar ways as you would use a database. To some extent Calc is prepared for this unless you split up equal data structures into separate sheets.

One date value in some cell keeps the information about the year, the quarter, the month, the day, the week and the week day of that value. Software (database software and Calc to some extent) can easily analyse your data according to these subcategories of a date field if you keep that information together in one adjacent database-like table. For instance, you can pulll any subcategory in any order of rows from a single table with no effort. Calc can even generate a new table with aggregations (sum, min, max, average etc.) for each month, year, quarter, week, week day and any other category pulled from a single table. This way you can compare months without writing a single formula.

There should be no blank values in a properly set up database anyway.
IF you would use a database instead of a calculator, you would see that a database handles blank values properly. Either the whole table row won't be stored because of the missing value or --if you allowed blank values for the respective column-- then any calculation or query on that value returns blank.

Storing database-like tables in spreadsheets is a huge mistake because your data are unsafe (high risk of total loss) and because even the best spreadsheets have limited database capabilities.
Nevertheless, Calc and Excel allow you to do so. All the related features are in the Data menu and you need to understand how to work with expanding or shrinking references so your entered data are not out of scope. All this is far, far easier to deal with when you have input forms with a proper database (no spreadsheet) in the background.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Jul 19, 2018 7:56 pm
by FrogFan
Thanks Villeroy.

I spent a lot of time thinking about putting this application into a database about a year ago. I built a data model, designed relationships among the tables, and created input forms for entering data about members, meetings, attendance, and guests. I anticipated creating several reports as well. At one point, I got really caught up trying to design data entry forms with junction tables. I figured that out but ended up with a form that was really awkward to use -- way more cumbersome than the worksheet I had. I gave up on OpenOffice Base in part based on observations you made on these forums about Base's functionality around forms, I think, and tried Brand X. I finally gave up on it, too, when I realized that the overhead I needed to learn was far out-of-proportion to the amount of value my user (my sister-in-law) would get out of such an application.

Calc (and Brand X) allow you to do a lot of things much easier using spreadsheets than developing a database application. As I've demonstrated to myself here and elsewhere, you must understand some of the quirkiness, and if you do, you can build a system that "mostly" works. That's what I have now. And again, this is for my sister in law. It's not intended as commercial software and it contains no sensitive information. If data gets lost, it's not that big of a deal. It's for a women's club.

I will give some thought to laying things out differently, along the lines you describe. I know I shouldn't use blanks. That's very clear now <smiles>.

Thank you again.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Jul 20, 2018 8:40 am
by Villeroy
Whatever you do, you must not use separate tables for September and October, not for Category A and Category B and not for John and Mary. If the data on the split tables look the same then they need to be merged into one table with an additional column for the distinction.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Thu Aug 23, 2018 1:20 pm
by y-greek
I use ""&cell to avoid zero result for empty cell.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 12:45 am
by Phillip
When I saw "=""&cell" I thought - let's try it!
When cell is blank this gives blank (ok an empty string of LEN zero),any number including zero gives the same number with a LEN correct, it is just displayed as a string (left-justified), but this can be easily changed by right justify.
Amazingly arithmetic also works - A1=2, B1=""&A1 shows 2, C1=5+B1 shows 7.
So is this the answer?
Alas no: =ISNUMBER(B1) is FALSE, and B1 is ignored for functions such as COUNT, AVERAGE etc.
NB. The fact that arithmetic works seems very odd to me as presumably B1 is a string value "2".
So if you want the cells to be proper numbers you still need IF(ISBLANK();""; .... ) to filter out unused cells.
Still an interesting idea - thanks.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 1:58 am
by Lupp
(This is not a contribution to any discussion above.)

Since the original questioner who created the thread years ago came back, I would like to state that the question how to get a "blank" (or "empty") cell with the help of a formula was posted in slightly different ways more than once not only in this forum.

Of course it is inevitable that a cell containing a formula cannot report TRUE if asked ISBLANK().

Thus I would specify the question to more precision:
Is there a formula referencing a single [Editing with respect to the post below by MrProgrammer:] BLANK cell with the effect that the cell containing the formula reports FALSE if asked ISNUMBER() and if asked ISTEXT as well?

The answer is: This depends on whether you use AOO or a (not too old) version of LibO.
In LibO the formula {=OFFSET(A2; 0; 0; 1; 1)} entered for array-evaluation into, say, B2 results in an empty looking cell answering FALSE on both the above mentioned questions. In AOO the result is as if =IF(A2="";"";A2) was used.

This is mainly for completeness. Using array formulae with OFFSET for such a minor task comes with disadvantages, of course.
There is a demo:

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 4:14 am
by MrProgrammer
Lupp wrote:Is there a formula referencing a single cell with the effect that the cell containing the formula reports FALSE if asked ISNUMBER() and if asked ISTEXT as well?
I presume Lupp intends to ask about a formula referencing an empty cell. Suppose A1 is empty. Enter =A1 in B1. =ISNUMBER(B1) is FALSE, as desired. =ISTEXT(B1) is FALSE, as desired.

Though not part of Lupp's question, one might object to the display of 0 in B1. If so, create a style named Empty with Numbers → Format Code → "". In B1 use Format → Conditional Formatting → Formula is → NOT(OR(ISNUMBER(B1);ISTEXT(B1))) → Cell Style → Empty → OK.

Though not part of Lupp's question, one might object that =COUNT(B1) is 1. I will be surprised if somone finds a formula for B1 with FALSE for =ISNUMBER(B1), FALSE for =ISTEXT(B1), and 0 for =COUNT(B1). Setting B1 to =IF(ISNUMBER(A1);A1;T(A1)) avoids the need to use conditional formatting to suppress a 0, is FALSE for =ISNUMBER(B1), is 0 for =COUNT(B1), but is TRUE for =ISTEXT(B1). This is what I use in my spreadsheets if I need to reference cells which may be empty.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 11:40 am
by Lupp
MrProgrammer wrote:...a formula referencing an empty cell. Suppose A1 is empty. Enter =A1 in B1. =ISNUMBER(B1) is FALSE, as desired. =ISTEXT(B1) is FALSE, as desired.

Thanks!

Strange. I am using Calc in many versions for decades now and never did experience this. I simply couldn't imagine such a misleading behaviour. This despite the fact that I had well experienced the keen disregard of the transitivity of the equality relation by spreadsheets.

Just a hint concerning another inconsistency created by the above quoted fact:

=IF(ISNUMBER(B1);MATCH(B1;C1:C50;0);"Number to match is missing")
Let now contain B1 the formula =B1 in the context given by MrProgrammer. The cell SHOWS zero (if not a workaround is applied), it answers TRUE if asked =(B1=0), and of course a reference to B1 returns 0 and can be used with MATCH()... It's a mess!

"Friends never should let friends use spreadsheet." If I remember correctly 'jrkrideau' told me this. He is right. What fatal day that was when such a completely distorted concept of being "pragmatic" was forced on a previously useful class of tools.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 1:47 pm
by y-greek
Phillip wrote:NB. The fact that arithmetic works seems very odd to me as presumably B1 is a string value "2".

If text cell evaluates to number, it works even in Excel 2003

But this topic, it seems to me (and I googled here with that problem), is about string "0" where you expect "" from reference to blank cell.
""&reference much shorter then IF(ISBLANK(reference);"";reference) and reference may be calculated twice.

In numeric context blank cell is always zero, but if we want COUNT, AVERAGE etc. to work, we can use 0+cell (shorter than IF(ISBLANK...)

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 2:34 pm
by Phillip
When I started this topic it was how to copy cells so that unused ones were still processed as unused - i.e. ignored by COUNT/AVERAGE etc, while arithmetic still worked.
=A1 gives a zero that is not ignored, while arithmetic works.
=&A1 gives text which is no good as numbers are also ignored, yet arithmetic works.
=0+A1 gives a zero - the same as the original problem.
=IF(ISNUMBER(A1);A1;T(A1)) does work - so this is the workaround. (IF(ISNUMBER(A1);A1;"") also works when only numbers are involved.
Just beware using the T(A1):
COUNT ignores text (unless just spaces), yet arithmetic gives an error!
Oh for some consistency!
NB. I was a programmer too, that is why I consider that a direct copy of an unused cell should still be processed as an unused cell - although I do understand the historical argument made by Keme.

Can I close this topic now?

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 3:35 pm
by keme
Phillip wrote:When I started this topic it was how to copy cells so that unused ones were still processed as unused - i.e. ignored by COUNT/AVERAGE etc, while arithmetic still worked.
...

Not quite. It was about having indirect references working like direct references.

When you copy a cell range to another range, the empty cells remain empty. There is no issue.

When you reference a cell range from another cell range, some operations give different results when dealing with the referencing cells instead of the referenced cells.

You can close a topic - sort of - by editing the initial post and add [SOLVED] in the subject line. This shows everyone - more or less - that you are satisfied with the outcome. The Solved tag is just a hint to other users, it does not stop anyone from responding, and does not hide the thread from searches.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Aug 24, 2018 9:05 pm
by Phillip
Sorry Keme, perhaps I wasn't clear:
By copy I mean the result of a function as in "=cell". Copying as in Copy & Paste is, as you say, no issue.
I thought of closing the topic as the developers are unlikely to change the code. Although I believe making "= blank cell" display blank instead of a zero, and behave like a blank cell for numeric puposes would not be particularly difficult. Particularly as "=blank cell" behaves as like a blank cell for text processing purposes.

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Jun 28, 2019 9:28 pm
by carl2k17
Seriously Guys?

I'm not even a noob and I found a solution for this, F the formula's

Head Over to:

Tools > Options > Open Office Calc > View > Untick Zero values

Job Done

CalcView.png
 Edit: Embedded the picture from carl2k17 -- MrProgrammer 2019-06-28 19:15 

Re: Referencing a blank cell gives a numeric zero

PostPosted: Fri Jun 28, 2019 9:49 pm
by RusselB
carl2k17 wrote:Seriously Guys?

I'm not even a noob and I found a solution for this, F the formula's

Head Over to:

Tools > Options > Open Office Calc > View > Untick Zero values

Job Done

Guide:
Code: Select all   Expand viewCollapse view
http://prntscr.com/o81xm8



This option simply suppresses the display of any cell that has a 0 value. While this does work for the situation indicated, namely referencing a blank cell, it also makes any calculation that returns a 0 value display as blank.
Thus looking at the display, it would be impossible to tell the difference between a cell that has the formula
Code: Select all   Expand viewCollapse view
=A3
where A3 is a blank cell, and where A3 contains a formula that returns a 0 value (eg: =3-2+2-3)

Re: Referencing a blank cell gives a numeric zero

PostPosted: Sat Jun 29, 2019 4:33 am
by Phillip
I had forgotten this post that I started back on March 13 2015.
Since it is so unlikely that =<BLANK CELL> will ever give a "Blank cell", I have decided to mark it [SOLVED]
to save any more repetition - Carl2K17 see all the posts from the start (now two pages).
Just one parting comment:
AVERAGE(), COUNT() etc, ignore blank cells, to include them you have to enter a zero.
AVERAGE(), COUNT() etc, include =<BLANK CELL>, to exclude them you have to use =IF(ISBLANK(A3);"";A3)!!
Thanks for all the fun folks!