Bug 145070 - Formula IF & LEFT(RIGHT) conflict
Summary: Formula IF & LEFT(RIGHT) conflict
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Formula Editor (show other bugs)
Version:
(earliest affected)
7.1.5.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-11 12:35 UTC by swhuang76
Modified: 2021-10-11 13:38 UTC (History)
0 users

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description swhuang76 2021-10-11 12:35:53 UTC 
Description:
IF
A1=10.5G，A2=10.2C
=IF(LEFT(A1,4)>LEFT(A2,3))=True

A1=10.5G，A2=9.7C
=IF(LEFT(A1,4)>LEFT(A2,3))=False

It seems that difference of number of characters would make formula IF & LEFT go wrong, and formula RIGHT is as well.

Steps to Reproduce:
1. Assume A1=10.5G，A2=9.7C
2. Formula IF(LEFT(A1,4)>LEFT(A2,3))=False
3.

Actual Results:
FALSE

Expected Results:
TRUE


Reproducible: Always


User Profile Reset: No



Additional Info:
please check the formula IF & LEFT
Comment 1 Eike Rathke 2021-10-11 13:24:53 UTC 
=LEFT(A1;4)  => 10.5 (string)
=LEFT(A2;3)  => 10.  (string)
=LEFT(A1;4)>LEFT(A2;3)  => TRUE
=IF(LEFT(A1;4)>LEFT(A2;3))  => TRUE
=IF(LEFT(A1;4)>LEFT(A2;3))=FALSE  => FALSE

Of course, this is correct and not a bug.
Comment 2 Mike Kaganski 2021-10-11 13:29:20 UTC 
There's no conflict here. You are comparing strings, not numbers; they are compared lexicographically.

When you compare string "10.5" to string "10.2", the latter is lexicographically before the former (its all but last characters are same, and last character in the latter is alphabetically before the one in the former).

When you compare "abcd" to "abca", the latter is lexicographically before the former, for the same reason.

When comparing "10.5" to "9.7", the latter is lexicographically after the former: they start from different characters, and that character defined the order.

When you compare "abcd" to "xyz", the latter is lexicographically after the former, for the same reason.

You need to use something like

VALUE(LEFT(A1,4))

Closing NOTABUG.
Comment 3 Eike Rathke 2021-10-11 13:38:44 UTC 
Oh, and for your varying test case (confusingly only in the repro steps) it is

=LEFT(A1;4)  => 10.5 (string)
=LEFT(A2;3)  => 9.7  (string)
=LEFT(A1;4)>LEFT(A2;3)  => FALSE
=IF(LEFT(A1;4)>LEFT(A2;3))  => FALSE
=IF(LEFT(A1;4)>LEFT(A2;3))=FALSE  => TRUE

Because operator '>' there does a string comparison where 9>1, not numeric.
So, also correct and also not what you claim it was.

If you want numeric comparison, use
=NUMBERVALUE(LEFT(A1;4);".")>NUMBERVALUE(LEFT(A2;3);".")