Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?

users
#1

In OpenOffice.org and LibreOffice 3.3 if you used =SUM() to find the total of
the numeric values in a row or column where some of the cells were blank or
just text, then it worked! Now I've just opened a spreadsheet with
LibreOffice 3.4 and #VALUE! is appearing everywhere.

Why the change? It brings back awful memories of the OpenOffice.org upgrade
from 1.1.3 to 2.0.0

#2

Hi. Just made a new sheet with blanks in the range and sum worked ok.
steve

#3

Hi Phil,

To start with your subject line: must be, looking at the number of people that downloaded and tested betas etc.

prholland wrote (04-06-11 11:54)

In OpenOffice.org and LibreOffice 3.3 if you used =SUM() to find the total of
the numeric values in a row or column where some of the cells were blank or
just text, then it worked! Now I've just opened a spreadsheet with
LibreOffice 3.4 and #VALUE! is appearing everywhere.

Just works fine for me in 3.4.0
So it must be some special case ..?
Any point in sending me a file (offlist)?

Why the change? It brings back awful memories of the OpenOffice.org upgrade
from 1.1.3 to 2.0.0

No change intended, I guess, but I can imagine that you do not long for that same 1.1.4>2.2.0 experience :wink:
Still, the huge rework of code, and other repository/merge/... changes that have been done the last months, will for sure lead to extra discomfort.
That is why we explicitly say that the 3.4.0 is for early adaptors. The 3.4.1 will solve many of the nasty bugs, if not all. And then there will be more bugfix releases in the 3.4. line.

Well, all a bit explanation. Mentioning bugs: you might also have a look if your specific problem has been reported already:
  http://wiki.documentfoundation.org/Development#Reporting_Bugs

Thanks,
Cor

#4

It must be a new feature :slight_smile:

I advise you to test 3.3 RC1 and update to 3.3 when it is released. Version
3.4 is not ready for real work as stated in the release announcement

http://nabble.documentfoundation.org/The-Document-Foundation-announces-LibreOffice-3-4-0-tt3019206.html

#5

Hello prholland,

In OpenOffice.org and LibreOffice 3.3 if you used =SUM() to find the total
of the numeric values in a row or column where some of the cells were
blank or just text, then it worked! Now I've just opened a spreadsheet
with LibreOffice 3.4 and #VALUE! is appearing everywhere.

That's odd. I don't get that. This works for me:

A1 is =SUM(A2:A7,B1:G1)
A7 is =SUM(52/4)
G1 is =SUM(365/52)

E1 and A5 are empty
A6 and F1 are text

   A B C D E F G
1 =SUM() 1 2 3 TEST =SUM()
2 1
3 2
4 3
5
6 TEST
7 =SUM()

#6

Hi :slight_smile:
I think "#VALUE!" appears when some duff value is trying to be shown, such as
trying to divide by 0 or trying to add nonsensical values together such as word
added to numbers and being shown in a cell formatted to show numbers. It's
likely there is a tpyo somewhere, either in the formula or in the values in the
table.

Regards from
Tom :slight_smile:

From: PLO <protect.libreoffice@inboxshield.co.uk>
To: users@libreoffice.org
Sent: Sat, 4 June, 2011 11:28:15
Subject: Re: [libreoffice-users] Calc: Has anyone tested the
backward-compatibility of LibreOffice 3.4?

Hello prholland,

> In OpenOffice.org and LibreOffice 3.3 if you used =SUM() to find the total
> of the numeric values in a row or column where some of the cells were
> blank or just text, then it worked! Now I've just opened a spreadsheet
> with LibreOffice 3.4 and #VALUE! is appearing everywhere.

That's odd. I don't get that. This works for me:

A1 is =SUM(A2:A7,B1:G1)
A7 is =SUM(52/4)
G1 is =SUM(365/52)

E1 and A5 are empty
A6 and F1 are text

   A B C D E F G
1 =SUM() 1 2 3 TEST =SUM()
2 1
3 2
4 3
5
6 TEST
7 =SUM()

--
Si (PLO)
#27176. Do Gee Owns Whir? ¶

Auxiliary Information:
• LibreOffice 3.4.0 OOO340m1 (Build:12)
  • Windows XP Pro 5.1.2600 Service Pack 3

--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be

deleted

#7

#DIV/0! ??

#8

Sometimes I have found that the "#VALUE!" is because the number doesn't fit
in the width of the cell. Making the width of the column has resolved the
problem.
*I doubt if the movie Black Swan was making fun of me in my Linux mascot
guise!*

#9

Hi :slight_smile:
I remember this happened in Excell at one point too. I vaguely remember there
was some obscure setting that had suddenly been changed from previous
updates/versions. I would post a bug-report and then start searching for it as
it's not ideal default behaviour!
Regards from
Tom :slight_smile:

From: Cor Nouws <oolst@nouenoff.nl>
To: users@libreoffice.org
Cc: ooo@hollandnumerics.com
Sent: Sat, 4 June, 2011 11:08:24
Subject: Re: [libreoffice-users] Calc: Has anyone tested the
backward-compatibility of LibreOffice 3.4?

Hi Phil,

To start with your subject line: must be, looking at the number of people that
downloaded and tested betas etc.

prholland wrote (04-06-11 11:54)
> In OpenOffice.org and LibreOffice 3.3 if you used =SUM() to find the total
of
> the numeric values in a row or column where some of the cells were blank or
> just text, then it worked! Now I've just opened a spreadsheet with
> LibreOffice 3.4 and #VALUE! is appearing everywhere.

Just works fine for me in 3.4.0
So it must be some special case ..?
Any point in sending me a file (offlist)?

> Why the change? It brings back awful memories of the OpenOffice.org upgrade
> from 1.1.3 to 2.0.0

No change intended, I guess, but I can imagine that you do not long for that
same 1.1.4>2.2.0 experience :wink:
Still, the huge rework of code, and other repository/merge/... changes that
have been done the last months, will for sure lead to extra discomfort.
That is why we explicitly say that the 3.4.0 is for early adaptors. The 3.4.1
will solve many of the nasty bugs, if not all. And then there will be more
bugfix releases in the 3.4. line.

Well, all a bit explanation. Mentioning bugs: you might also have a look if
your specific problem has been reported already:
http://wiki.documentfoundation.org/Development#Reporting_Bugs

Thanks,
Cor

-- - http://nl.libreoffice.org
- giving openoffice.org its foundation :: The Document Foundation -

-- Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be

deleted

#10

Actually I had never noticed that spreadsheets (all including Excel) ignore
text values mixed with numbers. This worries me a lot!

I work with 300.000+ line spreadsheets and if one line has a text value
(because of a typo) I wouldn't notice that.

Is there any setting that triggers a warning (instead of ignoring the
cell(s)) in this situation?

#11

The following screenshot is taken from a spreadsheet that previously showed
"00:00" in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing
"#VALUE!" (NB: all the cells are formatted as Time values, and the "text"
cells contain "-"):

http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png

#12

Hi :slight_smile:

I think those empty cells (that now contain - ) might be causing the error. If
you delete the - in just 1 row does that fix the formula to display correctly?
I think number cells can be formatted to show a - mark if empty but 3.4 might
have got confused and thought those - were keyed in rather than being part of
the formatting. It's a long time since i dealt with this sort of thing and that
was in Excel so i could easily be utterly wrong.
Regards from
Tom :slight_smile:

From: prholland <ooo@hollandnumerics.com>
To: users@libreoffice.org
Sent: Sat, 4 June, 2011 14:26:56
Subject: [libreoffice-users] Re: Calc: Has anyone tested the
backward-compatibility of LibreOffice 3.4?

The following screenshot is taken from a spreadsheet that previously showed
"00:00" in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing
"#VALUE!" (NB: all the cells are formatted as Time values, and the "text"
cells contain "-"):

http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png

--
View this message in context:
http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023141.html

Sent from the Users mailing list archive at Nabble.com.

--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be

deleted

#13

Hello prholland,

The following screenshot is taken from a spreadsheet that previously showed
"00:00" in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing
"#VALUE!" (NB: all the cells are formatted as Time values, and the "text"
cells contain "-"):

Tom is correct, just tried it. If the cell is formatted as time, you get
'#VALUE!' as '-' isn't recognised as a time value. Taking the '-' out and
leaving the cells empty works.

I've worked with time sheets in 3.3.2 as well but then I never used a hyphen
to indicate 'no hours'. Maybe it was a 'fix' in this new version.

#14

Hi :slight_smile:
Thanks PLO. Now that you have confirmed that is likely to be the issue i played
around with "Format Cells". I couldn't get an ideal answer that shows a - even
when the cell is empty but i could get one when the cell has 0 value.

Select the range of cells and from the top menus select
Format - Cells - Number
and look in the bottom box "Format code". The standard format for time is
HH:MM
which pedantically only covers +ve values. Adding something specific for -ve
values, such as
HH:MM;[RED]HH:MM
helps 'flag-up' if soemthing has gone badly wrong. The ; (semi-colon) allows
negative values to have their own formatting rather than just defaulting to
whatever +ve values have. A next ; allows us to set how we want 0 values to be
treated so
HH:MM;[RED]HH:MM ;-
puts a - instead of a 0 value. Note that values that are close enough to 0 to
be rounded to 0 are still shown as either black or red 00:00s in that example.

I tried adding more ; to see if that would let us give a - for an empty value
but it didn't work. I'm sure there is a tick-box somewhere to do that but it
doesn't seem to be in the format cells dialogue box in 3.3.2.
Regards from
Tom :slight_smile:

From: PLO <protect.libreoffice@inboxshield.co.uk>
To: users@libreoffice.org
Sent: Sat, 4 June, 2011 14:58:49
Subject: Re: [libreoffice-users] Re: Calc: Has anyone tested the
backward-compatibility of LibreOffice 3.4?

Hello prholland,

> The following screenshot is taken from a spreadsheet that previously showed
> "00:00" in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing
> "#VALUE!" (NB: all the cells are formatted as Time values, and the "text"
> cells contain "-"):

Tom is correct, just tried it. If the cell is formatted as time, you get
'#VALUE!' as '-' isn't recognised as a time value. Taking the '-' out and
leaving the cells empty works.

I've worked with time sheets in 3.3.2 as well but then I never used a hyphen
to indicate 'no hours'. Maybe it was a 'fix' in this new version.

--
Si (PLO)
#32955. Ego Sow Whir Den? ¶

Auxiliary Information:
• LibreOffice 3.4.0 OOO340m1 (Build:12)
• Windows XP Pro 5.1.2600 Service Pack 3

--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be

deleted

#15

Date sent: Sat, 4 Jun 2011 06:06:55 -0700 (PDT)

#16

Date sent: Sat, 4 Jun 2011 06:26:56 -0700 (PDT)

#17

Hi "prholland",

prholland schrieb:

The following screenshot is taken from a spreadsheet that previously showed
"00:00" in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing
"#VALUE!" (NB: all the cells are formatted as Time values, and the "text"
cells contain "-"):

http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png

The ODF spec has some rules for converting text to numbers. Those conversion is different whether the text occurs in a simple calculation with operator or the text occurs in a range in function SUM or similar. As LO claims to follow ODF spec, it has to respect this.

In future you have to respect, that + - * / ^ will not work on text. You have to ensure, that the operands are numbers.

In OOo this can be done by the function N. Unfortunately the spec makes it "implemention defined" what N does with text. In OOo N returns 0 for text, which I think is useful. In LO it returns #VALUE although the help saws it would return 0. So this behavior seems a bug to me in the N function. Otherwise I would have recommend to use N(C1)-N(B1) instead of C1-B1.

I notice, that Tom has already found the solution, to enter 0 and format it to show - .

Kind regards
Regina

#18

Michael D. Setzer II wrote:

If you use count on a range it only counts numeric cells, but
counta counts numeric and non-empty cells, so if they don't give
the same results for the same range, there is an error?

Yes, that would work but it forces me to do this verification for each
column. And still it won't tell me where the error is (although I can find
it with some filters)

It would be much better if there was some AI in these functions warning me
that "Data in Cell A12345 is not a number. What do you want to do? A) Jump
to cell A12345 and manually fix it or B) Ignore this warning and Sum all
other cells?"

Why not use the computing power of the PC to help us?

#19

One useful facility in this case is Value Highlighting. Go to View | Value Highlighting or press Ctrl+F8. The font colour for text (temporarily) becomes black, for numbers and other values blue, and for formulae green. (Formulae should be no problem, since it's possible to construct them sufficiently carefully that you can be sure of the type of the result, of course.) Repeat the process to toggle the facility back off.

Not that I'd want to go looking through 300,000 blue values looking for a rogue black one ...

I trust this helps.

Brian Barker

#20

Brian Barker wrote:

One useful facility in this case is Value Highlighting. Go to View |
Value Highlighting or press Ctrl+F8. The font colour for text
(temporarily) becomes black, for numbers and other values blue, and
for formulae green. (Formulae should be no problem, since it's
possible to construct them sufficiently carefully that you can be
sure of the type of the result, of course.) Repeat the process to
toggle the facility back off.

I didn't know that. Could be useful sometime :wink:

If I wanted to visually check I would use Conditional formatting and set
Font to Red and Bold and the Background to Bright Yellow :slight_smile:

But as you said, I really don't want to browse 300.000 lines to spot errors
:wink: