Error in Calc logical functions

users
#21

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

#22

+1

#23

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

#24

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