[Solved] Referencing a blank cell gives a numeric zero

Discuss the spreadsheet application

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Sun Jul 15, 2018 11:42 pm

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.
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 keme » Mon Jul 16, 2018 1:13 pm

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

Re: Referencing a blank cell gives a numeric zero

Postby Villeroy » Mon Jul 16, 2018 6:24 pm

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
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: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby MrProgrammer » Mon Jul 16, 2018 6:47 pm

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.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3839
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Wed Jul 18, 2018 1:31 pm

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.
Attachments
Zero is sometimes not a zero.ods
(15.13 KiB) Downloaded 38 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 » Wed Jul 18, 2018 1:59 pm

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.
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: 8328
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Referencing a blank cell gives a numeric zero

Postby Villeroy » Wed Jul 18, 2018 2:05 pm

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.
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: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby keme » Wed Jul 18, 2018 3:12 pm

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

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Thu Jul 19, 2018 12:47 am

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?
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 » Thu Jul 19, 2018 12:51 am

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) :?:
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: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby Zizi64 » Thu Jul 19, 2018 7:08 am

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.
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: 8328
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Thu Jul 19, 2018 2:13 pm

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?
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 19, 2018 3:55 pm

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.
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 Villeroy » Thu Jul 19, 2018 4:13 pm

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.
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: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Thu Jul 19, 2018 5:53 pm

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.
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 Villeroy » Thu Jul 19, 2018 6:47 pm

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.
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: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby FrogFan » Thu Jul 19, 2018 7:56 pm

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.
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 Villeroy » Fri Jul 20, 2018 8:40 am

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.
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: 27207
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referencing a blank cell gives a numeric zero

Postby y-greek » Thu Aug 23, 2018 1:20 pm

I use ""&cell to avoid zero result for empty cell.
LibreOffice 5.4 x64 on Windows 8.1 Pro
y-greek
 
Posts: 2
Joined: Thu Aug 23, 2018 11:50 am

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Fri Aug 24, 2018 12:45 am

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.
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 Lupp » Fri Aug 24, 2018 1:58 am

(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:
Attachments
fakeBlank.ods
(10 KiB) Downloaded 26 times
Last edited by Lupp on Fri Aug 24, 2018 11:17 am, edited 2 times in total.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Referencing a blank cell gives a numeric zero

Postby MrProgrammer » Fri Aug 24, 2018 4:14 am

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.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3839
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Referencing a blank cell gives a numeric zero

Postby Lupp » Fri Aug 24, 2018 11:40 am

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.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Referencing a blank cell gives a numeric zero

Postby y-greek » Fri Aug 24, 2018 1:47 pm

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...)
Last edited by y-greek on Wed Aug 29, 2018 2:38 pm, edited 1 time in total.
LibreOffice 5.4 x64 on Windows 8.1 Pro
y-greek
 
Posts: 2
Joined: Thu Aug 23, 2018 11:50 am

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Fri Aug 24, 2018 2:34 pm

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?
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 keme » Fri Aug 24, 2018 3:35 pm

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

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Fri Aug 24, 2018 9:05 pm

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.
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 carl2k17 » Fri Jun 28, 2019 9:28 pm

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 
OpenOffice 4 on Windows 7
carl2k17
 
Posts: 4
Joined: Wed Aug 09, 2017 1:53 pm

Re: Referencing a blank cell gives a numeric zero

Postby RusselB » Fri Jun 28, 2019 9:49 pm

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)
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: 5494
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Referencing a blank cell gives a numeric zero

Postby Phillip » Sat Jun 29, 2019 4:33 am

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

PreviousNext

Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests