Stuck with 3.3 forever?

Johnny_Rosenberg · December 3, 2011, 5:11pm

I installed Ubuntu 11.10 from scratch today on my Eee-PC and found
that the installed version of LibreOffice was 3.4.4 (my old install
had 3.3.4, which I installed from the deb files at the LibreOffice
download page).
One of the differences I noticed between 3.4.4 and 3.3.4 is that 3.4.4
can't calculate with empty cells, which I find horrible and very
surprising.

For instance, I have this formula in one cell (E10), copied to a
couple of thousands of cells in the same column:
=IF(OR(DAY(A11)>DAY(A10);C10="");"";IF(OR(B10="Lunch";B10="Läkarbesök");E9;C10+E9))

Depending on the value in other cells, the result can be a time or an
empty string (""). In this case, E9 is an empty string, so E10 becomes
”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because
of this.

Seems like the developers decided that it shouldn't be possible to use
empty cells for calculation, and I just can't help myself wondering
why. This is a very common task in my case, and if an empty cell is
not defined as 0, it will mean that most of my formulas will be very
much longer and way more complicated than they need to be with 3.3.
That is, more complicated, harder to edit and very much slower. Is
this really the best way to go? Why is handling an empty cell as 0 in
calculations such a bad idea? Am I stuck with 3.3 like forever ow or
is this going to change any time soon?

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Pedro_Lino · December 3, 2011, 5:31pm

Johnny Rosenberg wrote

Depending on the value in other cells, the result can be a time or an
empty string (""). In this case, E9 is an empty string, so E10 becomes
”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because
of this.

Can you provide an example file? This used to be a problem but it is fixed
in 3.4.4 (at least under Windows)

Tom_Davies · December 3, 2011, 5:42pm

Hi
Are the empty cells somehow defined as text-cells? Do they contain a space or a ' mark or formatted to be text?
Regards from
Tom

Johnny_Rosenberg · December 3, 2011, 5:51pm

Johnny Rosenberg wrote

Depending on the value in other cells, the result can be a time or an
empty string (""). In this case, E9 is an empty string, so E10 becomes
”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because
of this.

Can you provide an example file? This used to be a problem but it is fixed
in 3.4.4 (at least under Windows)

I could, but it's not needed, I think. I just did this simple test in
3.3.4 and 3.4.4:

Open LibreOffice Calc from scratch.

A1:
=""

A2:
=A1+1

In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays:
1

In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays:
#VALUE!

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Johnny_Rosenberg · December 3, 2011, 5:57pm

Hi
Are the empty cells somehow defined as text-cells? Do they contain a space or a ' mark or formatted to be text?

Depends on the situation, but I found that entering ="" into a cell
causes different results from 3.3.4 vs 3.4.4.
So why do that? you might ask. Well, the formulas in a column looks
something like =IF(SomeCase;"";IF(AnotherCase;SomeResult;SomeResult+TheCellAbove)),
so here and there, there will be cells that are equal to ="", and in
some cases those cells will be used for calculation with the same
formula.

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Pedro_Lino · December 3, 2011, 6:06pm

Johnny Rosenberg wrote

I could, but it's not needed, I think. I just did this simple test in
3.3.4 and 3.4.4:

Open LibreOffice Calc from scratch.

A1:
=""

A2:
=A1+1

In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays:
1

In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays:
#VALUE!

That is expected. Cell A1 contains an empty text string (not a value). When
you try to do a calculation with a text string you get an error. MS Excel
also displays #VALUE

TBH I think it was version 3.3.4 that was wrong...

mariosv · December 3, 2011, 6:12pm

In 3.4 only text which can be explicitly interpreted as number, is treated as number, and an space or an empty string is not a number.
You can use the N(e9) to avoid this.
=IF(OR(DAY(A11)>DAY(A10);n(C10));"";IF(OR(B10="Lunch";B10="Läkarbesök");E9;C10+N(E9)))

I think this avoid misleading with text as numbers.

Miguel Ángel.

  * Inglés - detectado
  * Afrikaans
  * Albanés
  * Árabe
  * Bielorruso
  * Búlgaro
  * Catalán
  * Chino
  * Chino (Simplificado)
  * Chino (Tradicional)
  * Croata
  * Checo
  * Danés
  * Neerlandés
  * Inglés
  * Estonio
  * Filipino
  * Finés
  * Francés
  * Gallego
  * Alemán
  * Griego
  * Haitiano
  * Hindi
  * Húngaro
  * Islandés
  * Indonesio
  * Irlandés
  * Italiano
  * Japonés
  * Coreano
  * Letón
  * Lituano
  * Macedonio
  * Malayo
  * Maltés
  * Noruego
  * Persa
  * Polaco
  * Portugués
  * Portugués de Portugal
  * Rumano
  * Ruso
  * Serbio
  * Eslovaco
  * Esloveno
  * Español
  * Swahili
  * Sueco
  * Tailandés
  * Turco
  * Ucraniano
  * Vietnamita
  * Galés
  * Yídish

  * Afrikaans
  * Albanés
  * Árabe
  * Bielorruso
  * Búlgaro
  * Catalán
  * Chino
  * Chino (Simplificado)
  * Chino (Tradicional)
  * Croata
  * Checo
  * Danés
  * Neerlandés
  * Inglés
  * Estonio
  * Filipino
  * Finés
  * Francés
  * Gallego
  * Alemán
  * Griego
  * Haitiano
  * Hindi
  * Húngaro
  * Islandés
  * Indonesio
  * Irlandés
  * Italiano
  * Japonés
  * Coreano
  * Letón
  * Lituano
  * Macedonio
  * Malayo
  * Maltés
  * Noruego
  * Persa
  * Polaco
  * Portugués
  * Portugués de Portugal
  * Rumano
  * Ruso
  * Serbio
  * Eslovaco
  * Esloveno
  * Español
  * Swahili
  * Sueco
  * Tailandés
  * Turco
  * Ucraniano
  * Vietnamita
  * Galés
  * Yídish

<javascript:void(0);>

David_S_Crampton · December 3, 2011, 6:33pm

I agree with Pedro. A null text is not to be treated as a numeric zero. An
empty cell may be calculated as a zero but a null text is not an empty cell.
If have found the #VALUE result of mixing null text within a calculation to
be a good debugging and alert technique that the values elsewhere in my
spreadsheet have become messed up.

Johnny_Rosenberg · December 3, 2011, 6:44pm

I agree with Pedro. A null text is not to be treated as a numeric zero. An
empty cell may be calculated as a zero but a null text is not an empty cell.
If have found the #VALUE result of mixing null text within a calculation to
be a good debugging and alert technique that the values elsewhere in my
spreadsheet have become messed up.

I could agree with that, but it would have been nice if they thought
of that since 1.0 and not changing it on the way, breaking a whole lot
of spreadsheets…

Let's assume we have this example:
A2:
=IF(A1=6;"";B1)

Now, in some cases, A2 will be an empty string, so =A2+1 will fail in
another cell, right? Is there another way to make A2 look empty in
this case, making it useable for calculations? Something like the
following:
=IF(A1=6;SomethingNiceToPutHere;B1)

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Johnny_Rosenberg · December 3, 2011, 6:48pm

In 3.4 only text which can be explicitly interpreted as number, is treated
as number, and an space or an empty string is not a number.

But what's wrong with having any kind of no-number-strings treated like 0?

You can use the N(e9) to avoid this.
=IF(OR(DAY(A11)>DAY(A10);n(C10));"";IF(OR(B10="Lunch";B10="Läkarbesök");E9;C10+N(E9)))

I think this avoid misleading with text as numbers.

Okay, that could be an acceptable solution, I'll try it as soon as
possible. Thanks!

Steve_Edmonds · December 3, 2011, 7:05pm

Thanks Miguel. I posted a similar problem to the list some months ago, received the explanation of the reasoning, but yours is an elegant solution.
Steve

planas · December 3, 2011, 8:09pm

Johnny Rosenberg wrote

Depending on the value in other cells, the result can be a time or an
empty string (""). In this case, E9 is an empty string, so E10 becomes
”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because
of this.

Can you provide an example file? This used to be a problem but it is fixed
in 3.4.4 (at least under Windows)

I could, but it's not needed, I think. I just did this simple test in
3.3.4 and 3.4.4:

Open LibreOffice Calc from scratch.

A1:
=""

A2:
=A1+1

In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays:
1

In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays:
#VALUE!

Try in A1 no data/empty cell and in A2 =A1+1, you will get 1 in A2. You are adding a text and number and the + operator is not used for concatenation.

This has to do with operator overloading, are other, similar, operations allowed when using '+'. Some may allow it and others may not, restricting its use to mathematical operations only. LO uses the '&' operator for concatenation.

If you use A1 = "" and A2 = A1 & 1 you get 1.

Pedro_Lino · December 3, 2011, 8:24pm

Johnny Rosenberg wrote

I could agree with that, but it would have been nice if they thought
of that since 1.0 and not changing it on the way, breaking a whole lot
of spreadsheets…

I see your point. But this was wrong.
It's not a change of plans it's simply a correction. And not fixing it would
break a lot of other spreadsheets

Johnny Rosenberg wrote

Is there another way to make A2 look empty in
this case, making it useable for calculations? Something like the
following:
=IF(A1=6;SomethingNiceToPutHere;B1)

There are at least two ways: add an extra IF to the following formula and
use ISTEXT to validate or replace SomethingNiceToPutHere by 0 (zero) and use
conditional formatting to hide the zeros (set the font to White or the cell
background color when the value is zero)

If you liked the way it worked before you can use =N() as suggested by
Miguel Angel.

David_S_Crampton · December 3, 2011, 9:39pm

Expanding a bit on Jonny's concatenation:

A1: "" (null string)
A2: 1 (numeric)
A3: =A1+A2 displays #VALUE
A4: =A1 & A2 displays left-aligned "1". It is a text result.
A5: =A2 + A4 displays a right-aligned 2. It is a numeric result.

Ergo, the "+" operator gave #VALUE result when combining a null string and a numeric but gives a numeric when combining a <text string which can be interpreted as a numeric> with a numeric.

Per my mini-sermon (earlier today) on debugging and preferring strict type-based behavior, I would prefer A5 to also display #VALUE.

Pedro_Lino · December 3, 2011, 9:55pm

David S. Crampton wrote

Per my mini-sermon (earlier today) on debugging and preferring strict
type-based behavior, I would prefer A5 to also display #VALUE.

Obviously! If it behaves differently it's simply inconsistent.

It the user wants to change that he just has to do =A2+VALUE(A4)

If there is a voting somewhere you can count +1 on the strict (coherent,
consistent) behavior.

mariosv · December 3, 2011, 10:14pm

El 03/12/11 22:54, Pedro escribi:David S. Crampton wrotePer my mini-sermon (earlier today) on debugging and preferring strict
type-based behavior, I would prefer A5 to also display #VALUE.Obviously! If it behaves differently it's simply inconsistent.

It the user wants to change that he just has to do =A2+VALUE(A4)

If there is a voting somewhere you can count +1 on the strict (coherent,
consistent) behavior.Also mine.But interpret text as number is only with direct reference, not
for example in the middle of a SUM().Miguelngel..

mariosv · December 3, 2011, 10:16pm

Also mine.
But interpret text as number is only with direct reference, not for example in the middle of a SUM().
Miguel Ángel.
.

planas · December 3, 2011, 10:20pm

The default behavior appears to be an implicit type conversion of string to a numeric data type when it is possible. Either to an integer or decimal as needed. I tried your test using 1.356 (US format) in A2 and got

A# = #VALUE
A4 = 1.356 as string (left justified)
A5 = 2.356 as decimal (right justified)

If the there is any character that can not implicitly convert to a number, an error is thrown. This probably dates back to the original spreadsheets (Visicalc and Lotus 123). For consistency one must follow some of the original, even if stupid, decisions so the average user is not confused and spreadsheets can be easily imported into other spreadsheet programs.

Johnny_Rosenberg · December 4, 2011, 9:18pm

Johnny Rosenberg wrote

Depending on the value in other cells, the result can be a time or an
empty string (""). In this case, E9 is an empty string, so E10 becomes
”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because
of this.

Can you provide an example file? This used to be a problem but it is
fixed
in 3.4.4 (at least under Windows)

I could, but it's not needed, I think. I just did this simple test in
3.3.4 and 3.4.4:

Open LibreOffice Calc from scratch.

A1:
=""

A2:
=A1+1

In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays:
1

In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays:
#VALUE!

Try in A1 no data/empty cell and in A2 =A1+1, you will get 1 in A2.

Well, I know that, of course. And that's not a part of the problem.
The problem occurs when a cell is set to an empty string. Older
versions considered the value empty strings to be 0, which was very
elegant. That feature now seems to be gone.

You are
adding a text and number and the + operator is not used for concatenation.

Very good, because concatenation is not what I want in this case.

This has to do with operator overloading, are other, similar, operations
allowed when using '+'. Some may allow it and others may not, restricting
its use to mathematical operations only. LO uses the '&' operator for
concatenation.

Which I use all the time when I want concatenation, which I don't in this case.

If you use A1 = "" and A2 = A1 & 1 you get 1.

Yes, but if A1=75, I get 751 and not 76, so that's not the ultimate
workaround in this case.

The point is that ALL of the cells in a certain column contains a
formula that, depending of other cells, give either an empty string as
result, or a numerical value.

Let's create another example:
Let's use A1 for manual input.
In A2, enter:
=IF(A1="";"";A1)
In A3, enter:
=A2+27

If you enter a number in A1, A3 displays that number+27. If you don't,
A3 displays an error message: ”#VALUE!” (I guess; I didn't test this
particular example…). I want it to display 27.

Thanks anyway for trying to help.

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Johnny_Rosenberg · December 4, 2011, 9:27pm

Seems like the N() function is the best solution in this case then.
Using a test every time I want to use a cell that MIGHT be an empty
string would make my formulas ridiculously long, and most of them are
quite long already.
Another solution would of course be to create a new cell formula with
LibreOffice Basic, that takes care of everything, but I guess it
would be rather slow, which I think would be noticeable in my case
since I we are talking about thousands of rows here.

The answer I was hoping for in this case, about if there was a way to
make a cell look empty, was that there is maybe some function to use
or something, something like EMPTYCELL():
=IF(Something;EMTYCELL();AnotherCell+AThirdCell)
Which is not a good idea, because if I empty the cell, I guess its
formula will be erased too…

Well, never mind, I think I'll go with N() anyway now. Short and simple.

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ