how to display blank cell in place of zero result

30 replies [Last post]
Tamarind
Offline
Last seen: 3 years 4 weeks ago
Title:
Joined: 18 Nov 2011
Posts: 3

I have many files that used a very simple formula, for example:

=IF(SUM(F25+K25+P25)>1,SUM(F25+K25+P25),"")

where "" should leave the cell blank if the sum was less than 1, as it did in OO.  However LibreOffice returned #VALUE in all cells with this formula.

The laborious workaround was to replace all  "" in formulas, with 0 using Edit > Replace All.

I now have spreadsheets full of zeros where there were nice blank spaces previously.

I hope that there is a fix for this
n/a
PeterRoots
Offline
Last seen: 1 year 6 weeks ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
Well one way is to use

Well one way is to use conditional formatting.

Highlight all the relevant cells then Format>Conditional Formatting

Condition 1 should have a tick then make sure "cell value" and "equal to" are showing and put 0 in the value.

for cell style click new style and create one (maybe call it NoZero or something) and, on the font effects tab, set the font colour to white. All your Zeros will now vanish (assuming you have a white cell background).

(You could, of course dispense with your formula altogether and just sum your values normally. Then use conditional formatting to colour any values, in the summed, cells as white if <1)

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

Tamarind
Offline
Last seen: 3 years 4 weeks ago
Title:
Joined: 18 Nov 2011
Posts: 3
Solved

Thanks PeterRoots, but for many spreadsheets that each contain 50 or so pages, that would be a very laborious and time consuming task, simply to achieve a visual style. 

The solution is SO SIMPLE, it is embarrassing to admit that I didn't know about it previously!

Tools > Options >Libreoffice Calc > View

and deselect 'zero values'.... doh!

At least I can mark this as SOLVED.


n/a
PeterRoots
Offline
Last seen: 1 year 6 weeks ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
Well knock me down with a
Well knock me down with a feather, never noticed that one hiding a plain view!!

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

ByteJuggler
Offline
Last seen: 2 years 30 weeks ago
Title:
Joined: 3 Dec 2011
Posts: 7
Hmmm, well can you though. 

Hmmm, well can you really mark this as solved though?  I don't think so and I'll explain why:  I've just coincidentally tonight opened up a Libre Office spreadsheet created on V3.3.2 having formulas that effectively add up other formulas that yield "" in some instances and which works fine on that version, on my other PC, which has version 3.4.1.  All of a sudden I have #VALUE in the column adding the blanks.... (this was because the 3.3.2 was slow on the XP box and I thought "I know, I'll edit it on my super fast Win 7 box...", bad idea due to this issue! :)

Reason why I don't really think this issue should be closed: The 0 value solution is not acceptable as this messes up averages taken over the column with the blanks.  If you replace the blanks with zero's the average is obviously modified....


Comments?


(Edit, PC with 3.3.2 is Windows XP, PC with 3.4.1 is Windows 7, in case it matters.  This problem is actually breaking a lot of my spreadsheets I notice now, as I use the "" pattern to generate a blank/non-value in a cell quite frequently.  There has to be a way to do this, or this is a bug IMHO...)

n/a
PeterRoots
Offline
Last seen: 1 year 6 weeks ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
your problem is not the same

your problem is not the same one as the one this thread was about, which was how to not display anything if the cell value was less than 1. The origianl solution was to use if/then to give a blank result and this was changed to give a zero result and solved by 'display zero values'.

your problem is that you used to be able to treat a blank cell (ie a nul) as a number zero and now you can't. Some would probably argue that the old behaviour was a bug, which you exploited, but this has now been fixed so you can no longer exploit it. Whatever the case, a different issue to the start of the tread. 

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

ByteJuggler
Offline
Last seen: 2 years 30 weeks ago
Title:
Joined: 3 Dec 2011
Posts: 7
PeterRoots wrote:your
PeterRoots wrote:

your problem is not the same one as the one this thread was about, which was how to not display anything if the cell value was less than 1. The origianl solution was to use if/then to give a blank result and this was changed to give a zero result and solved by 'display zero values'.


Sorry but that's not correct.  The original problem states:

Tamarind wrote:

I have many files that used a very simple formula, for example:

=IF(SUM(F25+K25+P25)>1,SUM(F25+K25+P25),"")

where "" should leave the cell blank if the sum was less than 1, as it did in OO.


The posters point about leaving the cell blank when less than 1 is coincidental, it is an example only.  Thus the problem relates to any formula which is meant to leave the resultant cell blank by generating a "" which then gets added to another cell elsewhere.  In this respect it is identical to my problem... (To be clear: I also have a formula, with a sum, that sometimes generates a blank string, and now are generating #VALUE.  The problem is identical.)


PeterRoots wrote:

your problem is that you used to be able to treat a blank cell (ie a nul) as a number zero and now you can't. Some would probably argue that the old behaviour was a bug, which you exploited, but this has now been fixed so you can no longer exploit it. Whatever the case, a different issue to the start of the tread. 

No, this is not correct either.  I was previously able to treat a formula yielding a blank ("") as actually being blank in the context of additions and sums.  My whole complaint is exactly that that the workaround proposed (replace "" by 0)  breaks other formulas like average which normally ignores blank cells as it should.  However, average (quite rightly) does not ignore zeros (0)!  To belabor the point: the average of (1,2,3) != average of (1,0,0,0,0,2,0,0,0,0,0,0,3).  (You therefore cannot just replace blank cells with zeros, it does not generate the same answers.)

Similarly most spreadsheets (the Old Open Office, Libre Office up to 3.3.2, MS Excel, Gnumeric, the list goes on) will treat an empty cell (whether generated or blank by default) as zero for the purposes of addition, and will ignore it for the purposes of averages and suchlike, whereas the newer LibreOffice does not allow the use of generated blank cells in sums and (as per this post) generates #VALUE, even though paradoxically and inconsistenly it does still ignore the additions of non-generated (ie by default) blank cells in the spreadsheet.

So, it is the exact same issue: I get #VALUE for certain sums in my spreadsheets that work fine on the older Libre Office, fine on Excel, etc, and breaks on the later version.   You misunderstood my reference to average() as being part of my problem -- it was not part of the problem, it is mentioned to demonstrate why the suggested fix (of manually changing "" to 0) is not acceptable, e.g. doing so breaks (for example) average().

I  guess the implication is that "" is not the same as Null/Blank/Empty cell from the spreadsheet's point of view, so is there a way of output a Null/Blank/Empty cell value from a formula such that it is treated identically to a cell that's not had a value entered yet at all in all instances?

n/a
PeterRoots
Offline
Last seen: 1 year 6 weeks ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
original question how to

original question

how to display blank cell in place of zero result

Can this now be done?

The solution is SO SIMPLE, it is embarrassing to admit that I didn't know about it previously!

Tools > Options >Libreoffice Calc > View

and deselect 'zero values'.... doh!

At least I can mark this as SOLVED.

Problem and solution are to do with DISPLAY of a blank or zero not the use of them in other equations, which yours is. Ignoring blanks (ie a blank string value) from a list of numbers stops you getting odd results in a average but if those blank cells are zero then they should be included. A blank cell is ambiguous - is it a blank cell or is it a zero value - should it be included in a mathematical equation or used in a string manipulation? You are expecting it to be either blank or zero according to what you want it to be. Your interpretation may differ from another persons (a missed value in a list, for example, should generate an error as a list of numbers is expected). To avoid this you need to be specific about what is in the cell. An empty string or a zero numeric value. This is not ambiguous but is not what you are used to.

http://help.libreoffice.org/Calc/Handling_of_Empty_Cells

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

ByteJuggler
Offline
Last seen: 2 years 30 weeks ago
Title:
Joined: 3 Dec 2011
Posts: 7
Peter, OK.  I was trying to

Peter, OK.  I was trying to point out there's a deeper issue here, and that this is arguably not just a display issue fundamentally, and secondly was hoping there's is some sort of alternate work around (maybe another "EmptyCell" value that one could output in a formula) to retain the previous behaviour that may have been added to this thread, that may have been of help to others who might find this thread in future.  This is not the case, and since you insist that this thread is supposed to begin and end at the display issue I'll leave it at this here, and open up another thread for my issue.

(Aside, I understand what you're saying w.r.t. blanks being ambiguous, however I submit that the current behaviour needlessly breaks both compatibility and a pragmatic common sense interpretation of handling this ambiguity of blanks that worked fine before in both Libre/Open Office as well as other spreadsheets. )

Anyway, thanks for your time. 

n/a
PeterRoots
Offline
Last seen: 1 year 6 weeks ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
Probably more likely to find
Probably more likely to find an answer in a more specifically titled thread which will also help others searching for a similar thing

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

whingknutt
Offline
Last seen: 2 years 30 weeks ago
Title:
Joined: 24 May 2012
Posts: 1
So is there a new thread?

So, is there a different thread to address ByteJuggler's issue?  Because for whatever reason, many of my sheets rely on functions where a possible result is "" (double quote).  The standard behavior for a "" when the cell is referenced by other functions is to treat the cell as if there wasn't a calculation in in it (default/null/blank/empty).  

Warning! Rant Below:

I had been using OOo and the first sheet I opened with Libre was complete junk.  I had to reinstall OOo just for Calc because I didn't have time to locate the problem (damn those deadlines!).

What's worse is that because this use is so common I didn't even consider it a potential problem.  Once I did get time to troubleshoot and found the problem I was gripped by disbelief that there isn't a documented alternative.  I'd be happy to replace every [""] with [empty], but there doesn't seem to be support for a function to result in a default/null/blank/empty cell at all.

The option to not display zeros is also faulty since I sometimes need to see that a function actually returns $0.00, or 0% or FALSE, or a ZERO instead of a blank.  If the two were equivocal then we would still be using roman numerals or some other number system that didn't have a 0. 

ByteJuggler
Offline
Last seen: 2 years 30 weeks ago
Title:
Joined: 3 Dec 2011
Posts: 7
Hello whingknutt, There's

Hello whingknutt,

There's this thread:

http://en.libreofficeforum.org/node/1888

The workaround I found (which is apparently not foolproof but was sufficient for what I was doing at the time) was to use Sum() with a range instead of adding cells individually as before. 

I still consider this issue to be a bug, or at least, asinine despite protestations to the contrary by others on this thread, for at least the following 4 reasons:

1) Breaks compatibility with other spreadsheets and spreadsheet conventions regarding this issue

2) Breaks compatibility with older versions of LibO and OOO itself...

3) Doesn't comply with common sense/reasonable interpretations as to what to do with cells with blank strings in them, which is indeed what other spreadsheets and LibO itself *used* to do. (Principle of parsomony, Principle of least surprise)

4) This behaviour causes different representations of the *same* mathematical operation (in my case specifically addition but there may be others) to behave differently on the same data cells (e.g. Sum() works one way, "+" works another, even though they've both doing exactly the same thing.)

I, like you, was as you put it "gripped by disbelief" that there apparently isn't some sensible alternative.   I still feel that way, and sadly for now am back to using Excel 2007 (despite the sum() workaround in my other post.)

n/a
skelem
Offline
Last seen: 2 years 1 week ago
Title:
Joined: 31 Oct 2011
Posts: 4
The mathematics of emptycell needs to be complete

There is a mathematics that needs to be complete to be useful: Use of an Empty in an equation, Use of an Empty in a complex function, Displaying an Empty, Testing for an Empty, and Generating an Empty

  • Use of an Empty: An empty cell, apparently, contributes to simple arithmetic (+,-,*,/) as a zero, but as non-existant for complex operators such as average. These are both good interpretations, as discussed above by Bytejuggler.
  • Displaying an Empty: The option of not displaying 0’s is wrong. Sometimes a sum is 0. You need to see that. The option should be “Display blank/empty as 0”.
  • Testing for Empty: There should be a way to test for an empty cell. isblank currently indicates if it’s really empty (TRUE), but not if an equation generates “” (FALSE).
  • Generating an Empty: There should be a way to generate an “empty” value, as in if(or(isempty(a4),isempty(b4)),EMPTY,a4*b4).

People build complicated spreadsheets that do more than just add or average a column of numbers. The mathematics of an EMPTY needs to be complete.

n/a
skelem
Offline
Last seen: 2 years 1 week ago
Title:
Joined: 31 Oct 2011
Posts: 4
Workaround methodology

I ended up using:


IF(OR(B3=" ",C3=" "),"  ",B3+C3)
p. 
n/a
fuksen
Offline
Last seen: 1 year 6 days ago
Title: ★★
Joined: 17 Sep 2012
Posts: 19
in some cases i need the cell

in some cases i need the cell to be empty if the value is not exist, not 0 because in the same time i need to do average, and if i put “” then subtract which will result #VALUE!.
i think “” or empty can not be categorize as string, because it does not exist or null. so addition of “” and 2 should be 2, not #VALUE!
1 Vote for old metode.

n/a
EarlsFurniture
Offline
Last seen: 33 weeks 5 days ago
Title: ★★★★
Joined: 17 Feb 2011
Posts: 71
You can't generate an empty or BLANK cell in the same cell as

the formula.

That would erase the formula – an impossibility trap.

What we need is a way to specify a target cell for formula results. Then you would be working on the result range, and not on the range that contains formulas.

“Empty” is not “blank” though both display nothing in the cell.

BLANK is truly and completely, a cell with absolutely nothing at all in it.

A cell which APPEARS empty may not be blank. It may have been conditionally formatted to have the text in the same color as the background. It may display the result of a formula in the cell, the result being a string of spaces, or the NULL string.

A cell with a formula in it, is NOT by definition “empty” or “blank.”

It has a formula in it.

When you are performing math on a cell that has a formula in it, you are effectively substituting that formula into your more simple one. Think of it as automatic algebra.

The cell with the formula is a variable which you’ll use in another formula. In reality, that variable, once defined, is either a value, or another formula. It can’t “not exist.”

There are very good reasons for the original math operators to not work on NULL or other strings. The programming language LO is built with as with most such languages, do not allow math operations on strings.

If you want to perform math on strings, or interpret strings and skip them when doing math, then you need to write your own custom function. The LO programmers did that with SUM, AVERAGE, etc.

These special functions do NOT treat visually empty cells as 0 or blank. They skip them entirely.

However, if a cell isn’t set to NULL, and it instead evaluates to 0 or FALSE, then some of these special functions WILL treat them as 0 – like AVERAGE does. To avoid this, you’d either have to redesign the sheet, or use AVERAGEIF and test for 0 or FALSE. (note, AVERAGEIF was added in LO 4.0, prior versions have to use SUM/COUNTIF or SUMIF/COUNTIF.)

I think the “math” of empty cells is just fine.

What would really solve many problems is the ability to specify target cells for results.

EarlsFurniture
Offline
Last seen: 33 weeks 5 days ago
Title: ★★★★
Joined: 17 Feb 2011
Posts: 71
In reply to fuksen, you already can do that.

Put “” in A1
Put 2 in B1
Put =SUM(A1:B1) in C1.

The result of the SUM formula is 2.

You cannot put in C1: =A1+B1

That will generate #VALUE!

This is because A1 is the NULL string. The NULL string is special. It is not ‘nothing.’ It is a text character. It does not “blank” or “empty” a cell. It only makes it APPEAR that way. You could achieve the same effect with conditional formatting and making text and background colors identical. This only “hides” the contents of A1 visually, it does not change the contents into nothing.

Thus, you get #VALUE! because you can’t add a text string to the number 2. The two data types are incompatible.

See my other reply on WHY you have to use SUM instead of +.

fuksen
Offline
Last seen: 1 year 6 days ago
Title: ★★
Joined: 17 Sep 2012
Posts: 19
how about this condition

Put =”“ in A1
Put =if(2=2;2) in B1
Put =if(3=3;3) in C1
Put =AVERAGE in C1.
Put =A1-B1 in B2
Put =A1-C1 in C2
Put =A1/B1 in B3
Put =A1/C1 in C3
etc formula.

say i put =0 in A1 then the average result is wrong.
what others function that can be use in instead of operator , something like sum() instead of + operator
so basically = – / * is for simple task only, for more complex i need to use function.

n/a
wolga
Offline
Last seen: 1 year 42 weeks ago
Title:
Joined: 28 Feb 2013
Posts: 1
Zero's required in blank cells?

I have a customer I just upgraded to LibreOffice4 and the first phone call was about the value thing.

A search quickly led me to this and other posts. I may have to downgrade her back to OpenOffice or perhaps she’ll just buy Office 2010. Not that she can’t afford it but I recommend to those who will never use anything but word wrap stay away from Word and stick with OpenOffice or now, LibreOffice4.

I too am gripped by disbelief and I have a question about this.

I have to think there is a reason for Libre programmers to disallow blank cells to be used as zero and require the user to fill dozens or hundreds of cells with a 0 (zero) for the sum of a series of cells to work properly. Especially since open office does it this way and LibreOffice is a fork of OpenOffice.

Is there a reason we’re being forced to enter zeros in blank cells? Is this the way it should be so not to break the path to 21st century spreadsheet code or was this just an arbitrary change? or is it a bug?

I can’t believe it’s a bug because it should have been squashed by now since it’s been around for quite a while.

For now, I am sticking with it since I have no reason to change but my customer has many spreadsheets and for her to modify all the spreadsheets to compensate for this change seems unreasonable.

I may need to move her back to OOo. I was looking forward to her being happy with LibreOffice4. This is quite a let down.

It would have been nice to have an auto fill option so when the issue is encountered, the effected cells can just magically fill with zeros at the users request.

We tried a search and replace and it does insert zeros in all the blanks but is also fills zeros in text cells. Plus it seems that doing this does not insert zeros in cells that are included in a series of summed cells.

I’m not as advanced as many accomplished spreadsheet users and yet I feel there could be a macro to sense a series of blank cells in a sum and offer to fill them with zeros. Just a guess.

Any guidance would be greatly appreciated.

-wolga

fuksen
Offline
Last seen: 1 year 6 days ago
Title: ★★
Joined: 17 Sep 2012
Posts: 19
does developers see this thread?

16000+ view and counting….

n/a
oweng
oweng's picture
Offline
Last seen: 6 hours 55 min ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2664
IMO this is not a problem

I think this thread has been well and truly answered, however there are a few things I want to point out. The first is this example:

Quote:
Put =”“ in A1. Put =if(2=2;2) in B1. Put =if(3=3;3) in C1. Put =AVERAGE in C1. […] say i put =0 in A1 then the average result is wrong.
This is wrong. An AVERAGE divides by the number of valid entries. Initially this would be 2. Placing the zero in A1 makes it 3.

Secondly:

Quote:
does developers see this thread?
Maybe. This is a user forum. If you are unhappy with the current behaviour, raise a bug report.

ChrisP
Offline
Last seen: 1 year 40 weeks ago
Title:
Joined: 14 Mar 2013
Posts: 1
"" leading to #value

I have read and re-read this thread. I also have the problem. It is not problem in other programs or versions.
The change in treatment from earlier versions causing what worked to fail and expecting people to rewrite formula if not bug is at least cavalier.

I have small spreadsheets with simple conditional formatting used to shade alternate lines and I could do try to make changes, or I can revert to Excel or earlier versions. I ought not to have to do either.

If bug report is open and I knew how to do so I would support it

oweng
oweng's picture
Offline
Last seen: 6 hours 55 min ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2664
Bugs are here

The related bug reports are FDO#42990 and FDO#59309. The former explains how behaviour is as intended and is specified in the ODF v1.2 specification. I personally do not think NULL, TEXT, and NUMBER values should be treated the same, but others are free to argue their cases.

EDIT: FDO#37132 appears to be an earlier version of the same bug (with the same result).

fuksen
Offline
Last seen: 1 year 6 days ago
Title: ★★
Joined: 17 Sep 2012
Posts: 19
yes it is at least for some people

sorry i typed a wrong formula, what i meant was this:

Put =”“ in A1
Put =if(2=2;2) in B1
Put =if(3=3;3) in C1
Put =AVERAGE (A1:C1) in D1.
Put =A1-B1 in B2
Put =A1-C1 in C2
Put =A1/B1 in B3
Put =A1/C1 in C3
etc formula.
the problem is #VALUE in cell B2 C2 etc

but when i tried to fix it say in A1 i enter =if(1=1;0) then the average result is wrong because of 0.
these example is not my real sheet, its just an simple example.

and yes i have read many bug report about this, but they declare that this is intended behavior, not bug.
im just a little upset because i have to use OoO for work just because of this. its holding me back.

n/a
oweng
oweng's picture
Offline
Last seen: 6 hours 55 min ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 2664
Average is not wrong

I want to be certain I am understanding you, because to me the calculated average is basic math.

Quote:
then the average result is wrong because of 0.
Average of NULL/TEXT [A1] + 2 [B1] + 3 [C1] is 2.5 i.e., 5 divided by 2. Average of 0 [A1] + 2 [B1] + 3 [C1] is 1.666 i.e., 5 divided by 3, as there are now three numeric values. That is how an average is calculated.

fuksen
Offline
Last seen: 1 year 6 days ago
Title: ★★
Joined: 17 Sep 2012
Posts: 19
the problem is

the average result should be Average of NULL/TEXT [A1] + 2 [B1] + 3 [C1] is 2.5.but the problem is not about average itself, the error #value that B2, C2, B3, C3 generate because operator does not compatible with “” or string.

so basically i have to choose between number 0 or string “” to work with formula, but both give me wrong result in different cell.
its not about the calculation, its about dilemma of the way “”, operator, and function act in libreoffice.

and yes its a basic math just so to make it simple to understand, but my sheet is far more complicate than this. thx for reply

n/a
PeterRoots
Offline
Last seen: 1 year 6 weeks ago
Title: ==Moderator==
Joined: 25 Oct 2011
Posts: 941
if you try to add string +

if you try to add string + number + number you will get an error because maths uses numbers not strings
0 is a number “” is a string

Linux Mint 15 LibreOffice 4.1.1.2 : OpenSUSE 12.3 LibreOffice 4.0.3

fuksen
Offline
Last seen: 1 year 6 days ago
Title: ★★
Joined: 17 Sep 2012
Posts: 19
well just got the answer,

well just got the answer, good news for this problem, just go to
option > libre office calc > formula > detail calculation setting > custom > treat empty string as zero: set to TRUE. and your problem is fix.
so long string!!!! at last i can upgrade my OoO3.4.1 to Libreoffice 4.0!!!! :cr Wink

AttachmentSize
Set String as Zero.jpg 66.64 KB
n/a
storyjesse
Offline
Last seen: 1 year 2 weeks ago
Title:
Joined: 3 Dec 2013
Posts: 1
THANK YOU!!!!!

Thank You So Very Much Fuksen!!!

I too use formulas containing empty strings “” as distinct from zero. Most notably because I find spreadsheets the quickest tool to create a wide range of customisable and highly adaptive printable forms.

Spreadsheets are great for this because the formulas allow for very basic programming with their if() statements while at the same time don’t require any knowledge of actually programming or scripting and can be printed and formatted with ease.

Bob Bostwick
Offline
Last seen: 33 weeks 5 days ago
Title:
Joined: 29 Apr 2014
Posts: 1
Consistency

This does indeed work for things written in calc, however if you attempt to insert a basic table in a document (writer) the calc menu is not avaliable for editing. Even if changed in calc, it will not effect writer documents, and there is no way to chance it in writer.

EarlsFurniture
Offline
Last seen: 33 weeks 5 days ago
Title: ★★★★
Joined: 17 Feb 2011
Posts: 71
Yep, this was added in the Options pane for Calc in March.

See Bug Report: https://bugs.freedesktop.org/show_bug.cgi?id=37132

As to the issue remaining in Writer documents with tables, I’d suggest filing another bug report, this time against Writer (since the other one has been fixed and was filed against Calc).

I’m sure with the code already in place, it would be a simple matter for the developers to have Writer check the Calc Options and behave accordingly. (after all, what’s the point of an ‘integrated suite’ if you have to repeat preferences multiple times across modules?)

Mind you, this option isn’t a solution to the ‘problem.’ It is a way to open older OOo and LO files, and Excel files that handle this differently. The Specified and Intended behavior of LO Calc (and eventually OOo Calc as well) is to NOT treat strings as numbers in all cases. The recommended approach for a new spreadsheet is to design it with this constraint in mind. I believe the Help and other documentation is slated for clarification on this point.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.