Re: [libreoffice-users] Stuck with 3.3 forever?

On 2011-12-04 07:12, MiguelAngel wrote:
> El 03/12/11 18:57, Johnny Rosenberg escribió:
> > 2011/12/3 Tom Davies<tomdavies04@yahoo.co.uk>:
> > > 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
> > ジョニー・ローゼンバーグ
> >
> >
> > > Regards from
> > > Tom :)
> > >
> > > --- On Sat, 3/12/11, Johnny Rosenberg<gurus.knugum@gmail.com>  wrote:
> > >
> > > From: Johnny Rosenberg<gurus.knugum@gmail.com>
> > > Subject: [libreoffice-users] Stuck with 3.3 forever?
> > > To: "LibreOffice Användare"<users@global.libreoffice.org>
> > > Date: Saturday, 3 December, 2011, 17:10
> > >
> > > 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
> > > ジョニー・ローゼンバーグ
> > >
> > > --
> > > For unsubscribe instructions e-mail to: 
> > > users+help@global.libreoffice.org
> > > Problems? 
> > > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: 
> > > http://wiki.documentfoundation.org/Netiquette
> > > List archive: http://listarchives.libreoffice.org/global/users/
> > > All messages sent to this list will be publicly archived and cannot 
> > > be deleted
> > >
> > > --
> > > For unsubscribe instructions e-mail to: 
> > > users+help@global.libreoffice.org
> > > Problems? 
> > > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: 
> > > http://wiki.documentfoundation.org/Netiquette
> > > List archive: http://listarchives.libreoffice.org/global/users/
> > > All messages sent to this list will be publicly archived and cannot 
> > > be deleted
> 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.
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

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted