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

classic Classic list List threaded Threaded
42 messages Options
Next » 123
prholland prholland
Reply | Threaded
Open this post in threaded view
|

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

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
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

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



On 4/06/11 9:54 PM, prholland wrote:

> 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
>
> --
> View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3022764.html
> Sent from the Users mailing list archive at Nabble.com.
>
Hi. Just made a new sheet with blanks in the range and sum worked ok.
steve

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Cor Nouws Cor Nouws
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by prholland
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 ;-)
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 [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by prholland
It must be a new feature :)

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
PLO PLO
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by prholland
Hello prholland,

On Sat, 4 Jun 2011 02:54:53 -0700 (PDT) (your time) you said:

> 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 [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

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

Hi :)
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 :)




----- Original Message ----

> From: PLO <[hidden email]>
> To: [hidden email]
> 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,
>
> On Sat, 4 Jun 2011 02:54:53 -0700 (PDT) (your time) you  said:
>
> > 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 [hidden email]
> In case of  problems unsubscribing, write to [hidden email]
> 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
>
>

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

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

On 04-06-2011 13:38, Tom Davies wrote:
> Hi :)
> I think "#VALUE!" appears when some duff value is trying to be shown, such as
> trying to divide by 0

#DIV/0!  ??

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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
Windows10 / LibreOffice  (latest?)
markku leitso markku leitso
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Tom
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!*



On 4 June 2011 13:38, Tom Davies <[hidden email]> wrote:

> Hi :)
> 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 :)
>
>
>
>
> ----- Original Message ----
> > From: PLO <[hidden email]>
> > To: [hidden email]
> > 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,
> >
> > On Sat, 4 Jun 2011 02:54:53 -0700 (PDT) (your time) you  said:
> >
> > > 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 [hidden email]
> > In case of  problems unsubscribing, write to
> [hidden email]
> > 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
> >
> >
>
> --
> Unsubscribe instructions: E-mail to [hidden email]
> In case of problems unsubscribing, write to
> [hidden email]
> 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
>

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Cor Nouws
Hi :)
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 :)




----- Original Message ----

> From: Cor Nouws <[hidden email]>
> To: [hidden email]
> Cc: [hidden email]
> 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 ;-)
> 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 [hidden email]
> In case of  problems unsubscribing, write to [hidden email]
> 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
>
>

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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
Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Cor Nouws
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?
prholland prholland
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by 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 Tom
Reply | Threaded
Open this post in threaded view
|

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

Hi :)

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 :)




----- Original Message ----

> From: prholland <[hidden email]>
> To: [hidden email]
> 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 [hidden email]
> In case of  problems unsubscribing, write to [hidden email]
> 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
>
>

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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
PLO PLO
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by prholland
Hello prholland,

On Sat, 4 Jun 2011 06:26:56 -0700 (PDT) (your time) you said:

> 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 [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

msetzerii msetzerii
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Pedro
On 4 Jun 2011 at 6:06, plino wrote:

Date sent:       Sat, 4 Jun 2011 06:06:55 -0700 (PDT)
From:           plino <[hidden email]>
To:             [hidden email]
Subject:         [libreoffice-users] Re: Calc: Has anyone tested
the backward-compatibility of
        LibreOffice 3.4?
Send reply to:   [hidden email]

> 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.
>

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?



> Is there any setting that triggers a warning (instead of ignoring the
> cell(s)) in this situation?
>
> --
> View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023116.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> Unsubscribe instructions: E-mail to [hidden email]
> In case of problems unsubscribing, write to [hidden email]
> 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
>


+----------------------------------------------------------+
  Michael D. Setzer II -  Computer Science Instructor      
  Guam Community College  Computer Center                  
  mailto:[hidden email]                            
  mailto:[hidden email]
  http://www.guam.net/home/mikes
  Guam - Where America's Day Begins                        
  G4L Disk Imaging Project maintainer
  http://sourceforge.net/projects/g4l/
+----------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS
SETI        10835199.920471   |   EINSTEIN     5971444.300851
ROSETTA      3209136.449584   |   ABC          6134395.303758


--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

msetzerii msetzerii
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by prholland
On 4 Jun 2011 at 6:26, prholland wrote:

Date sent:       Sat, 4 Jun 2011 06:26:56 -0700 (PDT)
From:           prholland <[hidden email]>
To:             [hidden email]
Subject:         [libreoffice-users] Re: Calc: Has anyone tested
the backward-compatibility of
        LibreOffice 3.4?
Send reply to:   [hidden email]

> 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 
>

In a quick test, this give the results you want?

=IF(AND(ISNUMBER(B7),ISNUMBER(C7)),C7-B7,0)


>
> --
> 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 [hidden email]
> In case of problems unsubscribing, write to [hidden email]
> 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
>


+----------------------------------------------------------+
  Michael D. Setzer II -  Computer Science Instructor      
  Guam Community College  Computer Center                  
  mailto:[hidden email]                            
  mailto:[hidden email]
  http://www.guam.net/home/mikes
  Guam - Where America's Day Begins                        
  G4L Disk Imaging Project maintainer
  http://sourceforge.net/projects/g4l/
+----------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS
SETI        10835199.920471   |   EINSTEIN     5971444.300851
ROSETTA      3209136.449584   |   ABC          6134395.303758


--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Tom Tom
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by PLO
Hi :)
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 :)






----- Original Message ----

> From: PLO <[hidden email]>
> To: [hidden email]
> 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,
>
> On Sat, 4 Jun 2011 06:26:56 -0700 (PDT) (your time) you  said:
>
> > 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  [hidden email]
> In case of  problems unsubscribing, write to [hidden email]
> 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
>
>

--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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
Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by prholland
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










--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by msetzerii
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?
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Pedro
At 06:06 04/06/2011 -0700, Nobody Noname wrote:
>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?

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


--
Unsubscribe instructions: E-mail to [hidden email]
In case of problems unsubscribing, write to [hidden email]
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

Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

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

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 ;)

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

But as you said, I really don't want to browse 300.000 lines to spot errors ;)
Next » 123