Re: [libreoffice-users] Error in Calc logical functions

Hello Regina

I have ** inlaid my answers, its good to see such a different and more 
apt perspective, one I am going to have to get used to.

thanks
---------------------------------------

On 13/07/2011 14:14, Regina Henschel wrote:
> Hi John,
>
> John B schrieb:
> > Dear Regina
> >
> > The way LO explains (you mention), its not a bug, but that does not mean
> > that it is correct either and can be classed as a false positive.
> >
> > It is not until you have practical use.
>
> LO follows the standard ODF and acts the same as main competitors.
> There are some aspects,
> (1) You can argue, that the standard should be changed to specify the 
> behavior directly and do not leave it to the implementations. If you 
> think so, you should write an request to OASIS.
> (2) You think, that LO should behave different to Excel, OOo, and 
> Gnumeric. Then you need very good rationals.
> (3) You want to achieve something, but might have used a wrong way. 
> Then you should first explain, what you want to achieve. It is likely, 
> that you need different formula.
** Yes I fully understand this, its just when I are used to Lotus 123, 
then I can understand why you think the way you do; and yes I do think 
(wish)  that LO should emulate 123. In 123, in the main, does not need 
to do any cell tweaking, it does it all for you.

** Who is / are OASIS?

> > For example if a box ="" (say in B1 which means empty)
>
> "box" is "cell"?
> If you write ="" into a cell, the cell is not empty. Test with 
> ISBLANK() will result in FALSE and tests with ISFORMULA() and ISTEXT() 
> will result in TRUE. It is a common error to consider an empty string 
> as empty cell.
>
>  then box stays blank
** I can see why you would need all the" IS xyz" functions in LO and 
Excel, although Excel is not something I would hold up as a good example.
**even in LO "" still means empty cell, for the purposes of the sheets 
default  mathematical calculation mechanism (not for other purposes - 
agreed).

**try in cells * E9* =IF(D9="","","full")  and then in* F9*  
=IF(E9="","empty","full") then type in a number / letter into *D9* and 
then delete* D9 *(toggle)* *you will see what I mean .

> > but if box A1 = a number, then a set amount appears in B1 (in business
> > most people do not like a page full of zero's and unused amounts
> > appearing for no good reason)
>
> Then they should format the cell not to show a zero.
** This will not work where you have an amount showing (the false 
Positive) formatting it to No zero's will only help when a zero appears 
(how annoying)
** a new command is needed  ISFALSEPOSITIVE then ignore!

> > What you _don't_ want to happen is as per LO, if you place any Letter in
> > A1 then the amount shows
> >
> > eg (a very common formula)
> >
> > in B1 =IF(A1=0,"",4.5) - which happens if you put any non number (a
> > space) in A1 even by accident, B1 will show 4.5 (a false positive).
>
> So the formula is unsuitable, use =IF(N(A1)=0;"";4.5) for example. But 
> as mentioned above, not showing a zero is a matter of formatting and 
> an empty string is still a string and not a value for to use in number 
> calculations. 
** This is only unsuitable in LO (and your stated industry standards)- 
please accept that again in 123 it is all taken care for you. I have 
used 123 for nearly 20years!
** I can see I am going to have to think how the LO sheet works 
before:-  how I want it to work.

> > Also if you now add up the row B with false positives, that would give
> > the wrong answer as well.
> >
> > As mentioned before in Lotus 123 this does not happen.
>
> That is the crux with "implementation-defined".

** LO / Excel  "implementation-defined"(whatever that means?) is such 
hard work!
> > However, I suppose it depends on your point of view and the software you
> > are used too. But it does mean that in the case Alejo showed, it is a
> > false positive, which has to be manually formula-ed out.
>
> It is the old problem with hidden, automatic conversions and the same 
> old rule "Never calculate with strings", but do explicit conversions 
> before.

** There is a fundamental difference between the two method of operation 
- which I think is the crux of the matter
** In cell say in A3 you have the formula A1+ B1 and the purpose is to 
add numbers (which is  99% of the time the purpose in spread sheets)
**In LO    1 + a = #value!  (error) > work needs to be done!
**In 123   1 + a = 1   (correct) > with no messing about
**Hence >/ It is the old problem........ //and the same old rule "Never 
calculate with strings"/ - never applied to me, as the original "old" 
industry standard spreadsheet took care of it.

**This is great (and good),  because you can "simply" type in text in 
the middle (say adding up a column in D11  =Sum(d1:d10)   then in D6, 
now if you enter  Nil or Not in Stock or  FOC etc,    123 simply ignored 
it. Try that with LO? you get the/* #value!*/ in the answer box (cell). 
and maybe:- Who is going to fix it? - that's why I think its a bug - not 
because of industry standard software, but because mathematical 
manipulation is a spreadsheets prime (or majority)  function and 
therefore should ignore text until "you" tell it to do so.


> > I cannot see any case where the reverse would be of practical use, if a
> > box is expecting a number and you enter in a letter then "nothing"
> > should be the result - In which case its a bug - unless - you know 
> > better?
>
> If you want a cell to only expect a number, you have to set up the 
> cell accordingly before entering something, otherwise a cell expects 
> all input.

**In LO, I am beginning to realise that there are further consequences 
to this and its NOT just that "to set up the cell accordingly" needs so 
much extra work,  its any other cells that looks at it as well.

** My (plus 10's of thousands) problem is that IBM have ditched Lotus 
123 in favor of their new IBM Symphony (OOo), so I really want/need to 
learn LO., but  at this time it feels like swapping a right hand  for a 
left hand drive - and I have to peddle and use hand signals as well!

> kind regards
> Regina

**Regards   John B

--
Unsubscribe instructions: E-mail to users+help@global.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/global/users/
All messages sent to this list will be publicly archived and cannot be deleted