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

Hi John,

John B schrieb:
[..]
> ** Who is / are OASIS?

OASIS is the organization, which prepared the ODF1.0 specification, 
which then became ISO standard. Now OASIS works on ODF1.2. This is 
currently in puplic review. For details and download links see
http://lists.oasis-open.org/archives/office/201106/msg00061.html


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

The comparison D9="" in cell E9 is evaluated as comparison <text>=<text> 
because "" is a text. Therefore the reference D9 is resolved to a text. 
So the rule in 6.3.14 in "Open Document Format for Office Applications 
(OpenDocument) Version 1.2 Part 2" is applied, which is,
    If the expected type is Text, then if value is of type:
    [..]
    Reference: perform conversion to scalar. If the referenced cell
    is empty, treat as an empty string (a text value with length 0).

Therefore the comparison D9="" resolves to TRUE, if D9 is empty.

[..]
> ** 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.

LibreOffice is designed to work as specified in the ODF standard. So we 
are not totally free, how to calculate.

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

"implementation-defined" means, that there is no rule in ODF standard, 
but each application may implement it in its own way.

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

The term 1+a is mathematically nonsense, so the term "correct" is 
questionable. But I agree, that it is handy to have an automatic 
conversion to zero in this case. This discussion arises from time to 
time, but before LibreOffice can change its way to calculate it, the ODF 
standard would have to be changed.

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

That's not correct. The function SUM is defined in a way, that it 
ignores empty or text cells inside a range. The problem is only with the 
operator +. You will get no error for =SUM(D1:D10).


> [..]  > ** 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!

We will do our best to help you getting familiar with LibreOffice.

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