Error in Calc logical functions

users
#1

Hi all, this is a little example to show what is going on.
In A column I have a numeric list from 1 to 21, in the middle I have a
letter, lets put A.
If a run this function in the next column =IF(A1>10), this function
evaluate the letter A like TRUE, why?

1 FALSE
2 FALSE
3 FALSE
4 FALSE
5 FALSE
6 FALSE
7 FALSE
8 FALSE
9 FALSE
10 FALSE
11 TRUE
A TRUE
13 TRUE
14 TRUE
15 TRUE
16 TRUE
17 TRUE
18 TRUE
19 TRUE
20 TRUE
21 TRUE

Thanks a lot in advance

C.

#2

Hi :slight_smile:
In ascii code the alphabetic characters are 'higher' than the numbers. So A is
calculated as being higher than any number. Remember that the machine doesn't
really understand letter or numbers as we read them. They are all just binary
digits. the binary digit that represents the character A is 'higher' than one
representing a number.

Regards from
Tom :slight_smile:

#3

I can repeat this:-

and therefore it is actually very wrong, A is not greater than 10, hence it should read False

I used Lotus 123 to check this out and is comes up False in 123

regards

John B

#4

Tom

That was the very 1st thing I thought, so I ran charmap on Arial and chose an accii code less than a number such as # and it still comes up TRUE.

It should either come up as False or Error

A bug I think

regards

John B

#5

no, it does not
from the line which has 11 in the first column the answer shows 'TRUE'

Your usage of the function IF() is wrong, there should be three parameters:
IF (test, then_value, otherwise_value)

try adding in column c: A1-10

if the result is <= 0 then => FALSE
if the result is >=1 then => TRUE

#6

Alejo C.S. schrieb:

Hi all, this is a little example to show what is going on.
In A column I have a numeric list from 1 to 21, in the middle I have a
letter, lets put A.
If a run this function in the next column =IF(A1>10), this function
evaluate the letter A like TRUE, why?

1 FALSE
2 FALSE
3 FALSE
4 FALSE
5 FALSE
6 FALSE
7 FALSE
8 FALSE
9 FALSE
10 FALSE
11 TRUE
A TRUE
13 TRUE
14 TRUE
15 TRUE
16 TRUE
17 TRUE
18 TRUE
19 TRUE
20 TRUE
21 TRUE

The ODF1.2 spec says ("functions" are here the comparison operators),
"These functions return one of True, False, or an Error if Left and Right have different types, but it is implementation-defined which of these results will be returned when the types differ."

And OpenOffice.org and LibreOffice have the simple rule "number is less than string". Excel and Gnumeric have these results too.

Kind regards
Regina

#7

Alejo

The strange thing is is that if you reverse the logic such as =IF(A1<11 and then reverse the True & False

You get the same results table and but with A still being TRUE

I am convinced this is a bug, which stops you writing an IF within an IF such as =IF(A1<11,"False",IF(A1>10,"True","False")) which should work - but it doesn't.

regards

John B

#8

Got it

Regina is correct, so:-

=IF(A1>50,"False",IF(A1>10,"True","False"))

Where 50 (or 1000) is higher than any number in your table

regards

John B

#9

Hi :slight_smile:
It might e worth posting a bug-report
http://wiki.documentfoundation.org/BugReport
The advice in the guide might be useful but far more useful is the link to post
a bug-report.
Good luck and regards from
Tom :slight_smile:

#10

Hi Tom,

Tom Davies schrieb:

Hi :slight_smile:
It might e worth posting a bug-report
http://wiki.documentfoundation.org/BugReport
The advice in the guide might be useful but far more useful is the link to post
a bug-report.

Where do you see a bug? For me the behavior is correct.

Kind regards
Regina

#11

The "IF" serves no purpose here: use =A1>10 instead.

Brian Barker

#12

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.

For example if a box ="" (say in B1 which means empty) 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)

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

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.

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.

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?

regards

John B

#13

If you think "A is not greater than 10", do you think it is less than 10 or equal to 10?

;^)

Brian Barker

#14

Hello Brian

Its none of those, they are not comparable, Ones a text the other is an amount. Therefore A logically is not greater than 10 stands (I hope)!

As stated, I used Lotus 123 as my Judge and Jury.

John B

Ps who is John Bonly?

#15

As Regina pointed out, the case of mixed number and text values in a comparison is defined by the implementation so that numbers are less than text (though that is surprising). That is, the result is as-designed, and this is consistent with the applications that LibreOffice provides compatibility with.

For ODF 1.2 OpenFormula, if that is being used, the result is also implementation-defined.

There is a precedent for the odd result. In some contexts, when there is a text and a number, the number is converted to text and a text comparison is made. In the example given, that would also have the indicated result. If the principle were to convert the text to a number, one would expect an Error Value because 'A' is not a valid expression for a number.

In any case, once a practice is established (how long have OpenOffice.org and LibreOffice Calc been doing this?), that becomes a case that some have already worked around. If the practice were changed, those existing calculations could be invalidated (unless the solution is to produce an Error Value).

So, to be concrete: Is the request here that 'A'>10 produce an Error Value?

- Dennis

#16

Hi :slight_smile:

I thought that when the equation was edited we were getting strange results.

10 = True

A<10 = True
Or was it False each time suggesting the A=10 despite A not being a set
numerical value.
Regards from
Tom :slight_smile:

#17

Am 12.07.2011 19:39, John B wrote:

Tom

That was the very 1st thing I thought, so I ran charmap on Arial and
chose an accii code less than a number such as # and it still comes up
TRUE.

The character "1" is not the same value as the number 1.
Any number is smaller than any number.

#18

I had another play with this LO logic buster this morning.

Whilst in Lotus 123 would only need @IF(A1>10,"True","False")

the way LO works the formula needs to be:-( placed in say B1 looking at cell A1) to cater for LO's rationale and possibly to cover prior versions.

=IF(A1="","",IF(A1>N,"",IF(A1>10,"True","False")))

N has to max out above any number in your list (eg 10000000) as it would appear that any Letter is seen by LO as greater than the highest possible number, to block this and to prevent the table seeing Letters as Numbers you need N. With N in place, if you type in a letter into cell A1, then B1 remains blank and does not to give a false positive (or negative).

regards

John B

#19

How about this formula.

=IF(ISNUMBER(A1),IF(A1>10,"True","False"),"Non-numeric")

                                                                   1
False

                                                                   2
False

                                                                   3
False

                                                                   4
False

                                                                   5
False

                                                                   6
False

                                                                   7
False

                                                                   8
False

                                                                   9
False

                                                                  10
False

                                                                  11
True

A
Non-numeric

                                                                  13
True

                                                                  14
True

                                                                  15
True

                                                                  16
True

                                                                  17
True

                                                                  18
True

                                                                  19
True

                                                                  20
True

                                                                  21
True

Date sent: Tue, 12 Jul 2011 20:14:27 +0200

#20

Michael

Excellent, I did not know ISNUMBER existed, I never needed it before, but for my usage I would remove the word "Non-numeric" and just leave "" so the cell would remain blank.

I can see that in LO or any other spreadsheet that treats Letters as Numbers, this would be a necessary function.

I have learned something today - thanks

John B