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

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.

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

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

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

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

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

kind regards
Regina

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