Libre Office not parsing a nested IF command correctly, that contains FIND commands

berghsg · February 6, 2025, 9:15pm

INTRODUCTION:
I have saved the spreadsheet as .ods
example of non duplicate code: N$6 = CUT01 N$5 = “” (enter CUT01 in B116 and 77 in D116)

example of duplicate code: PL$6 = DG09 PL$5 = 40cm
PM$6 = DG09 PM$5 = 35cm
PN$6 = DG09 PN$5 = 30cm (enter DG0930cm in B116, and 30cm in C116, and 77 in D116)

In Microsoft Office the following formula works for non duplicate code and duplicate code:
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))

NOTE: the above formula is in the cell ref PN116 for this case
Other formulas like it are in cell refs N116, PL116, PM116

For non duplicate code
CORRECT OUTPUT: N116 = 77

For duplicate code
CORRECT OUTPUT: PL116 = 0 PM116 = 0 PN116 = 77

HOWEVER ++++++++++

In Libre Office the following only works for non duplicate codes
=-1*(IFERROR(IF(FIND(N$6,$B116,1)>0,$D116,0),0))
CORRECT OUTPUT: N116 = 77

In Libre Office the following only works for duplicate codes
=-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 )))
CORRECT OUTPUT: PL116 = 0 PM116 = 0 PN116 = 77

COMBINING ‘FORMULA THAT WORKS WITH NON DUPLICATE CODE’ WITH ‘FORMULA THAT WORKS WITH DUPLICATE CODE’, USING ‘IF’ COMMAND, CONDITION: N$5>0

In Libre Office the following only works for non duplicate code
=IF(N$5>0,(-1*(IFERROR(IF(FIND(N$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(N$6,$B116,1),FIND(N$5,$C116,1))>0,$D116,0),0 ))))
CORRECT OUTPUT: N116 = 77

=IF(PN$5>0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
For duplicate code the INCORRECT OUTPUT: PL116 = 77 and PM116 = 77 and PN116 = 77, where only PN116 must = 77

In Libre Office the following only works for duplicate code
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
CORRECT OUTPUT: PL116 = 0 PM116 = 0 PN116 = 77

=IF(N$5=0,(-1*(IFERROR(IF(FIND(N$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(N$6,$B116,1),FIND(N$5,$C116,1))>0,$D116,0),0 ))))
For non duplicate code the INCORRECT OUTPUT: N116 = 0, where N116 must = 77

Why?

HOW LIBRE OFFICE SHOULD PARSE:
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))

IF PN5 contains a value, in this case 30cm the opening IF command should return a ‘false’ which in this case is the formula:
(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 )))
This above formula already works correctly by using ‘find’ to compare value in PN6 with what was entered in B116, a match occurs when the value counting from the first digit in B116 matches the value counting from the first digit in PN6 …in this case the value is ‘DG09’

AND

‘find’ compares value in C116 from the first digit with value in PN5, from the first digit. SO if a match is found in PN6 and PN5 as compared to what was entered in B116 and C116 then the value entered in D116 is returned as a ‘true’ statement, in this case 77 in PN116

Any help in getting the long formula to work for both non duplicate codes and duplicate codes in Libre Office will be appreciated. Since it DOES WORK in Microsoft Office, as is.

mariosv · February 7, 2025, 1:14am

Please attach a sample file, reduce the size as much as possible without private information, and paste the information in Menu/Help/About LibreOffice, there is a copy icon.

berghsg · February 7, 2025, 3:44pm

Hi Mario, i will send a sample spreadsheet as soon as possible.

berghsg · February 8, 2025, 7:09pm

Hi Mario

I tried finding the option to use ‘copy icon’ and I cannot find it. Please post the exact link. I do not see a ‘Help’ option. The only place I found in ‘About’ when I click on the top right ‘3 horizontal line icon’ is the following:
I see is a task bar at top

[About]
[FAQ]
[Terms of Service]
[Privacy]

Admins and Our Moderators

I have a sample file ready to upload. As an alternative I made a sample file available on my one drive.

await your further assistance

Thanks

Steph1 · February 8, 2025, 8:12pm

To upload a file here, use this icon from the toolbar of the comment box:

Now, about your file. I have copied your file to my drive, then I have saved it (downloaded) as an .xlsx file, then as an .ods file. I have opened both files in Calc (version 24.8.4.2). If I compare the contents of the cells PL116, PM116, PN116 in the original file with the file downloaded as .xlsx and .ods, I obtain exactly the same values, and this for the three sheets. In short, I do not see any difference.
Therefore, could you please identify in the original file which cells give a correct answer in excel and don’t give a correct result in Calc?

mariosv · February 9, 2025, 2:08am

berghsg · February 9, 2025, 6:48am

Hi Steph1

The cell references that give the correct answer in MS Excel are the following:
Sheet1: N16, PN116

using the formula
=IF(N$5=0,(-1*(IFERROR(IF(FIND(N$6,$B16,1)>0,$D16,0),0))),(-1*(IFERROR(IF( AND(FIND(N$6,$B16,1),FIND(N$5,$C16,1))>0,$D16,0),0 ))))
and
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
respectively

MS Office uses the same cell references.

The expected CORRECT output (w.r.t. Sheet1 above):
N16 = -77
PL116 = 0, PM116 = 0, PN116 = -77

WHEN

entering the values:
B16 = CUT01, D16 = 77
and
B116 = DG09, C116 = 30cm, D116 = 77

mikekaganski · February 9, 2025, 7:22am

Calc gives the same values as Excel (and of course, for a sample, instead of “scroll to infinitely far point”, it would be much better to just delete unneeded columns)
sample-file.ods (24.8 KB)

berghsg · February 10, 2025, 1:56pm

Hi Mike

The reason why I had so many uneccessary columns, is so that I can simulate exactly what I had in the original sheet, the cell refs and the formulas refering to those cell refs.

Concerning your test, assuming that between the top one and bottom, the one is saved as ods and the other as xlsx file.

I rechecked the calculations on Sheet 1 and the universal formula calculates correctly for duplicate and non duplicate codes.

However almost 2 weeks ago I was working with this same universal formula on my original sheet, and for some reason it kept returning a 0 instead of -77 for non duplicate code, inspite of entering CUT01 in B16 / B116 and 77 in D16 / D116.
My original sheet was worked on in Libre Office and MS Office. I did find a problem in saving formatting when working in Libre Office (after doing prior work in MS Office), wondering if perhaps the way formulas were processed was also being affected by MS ‘dark Magic’. ??

For now I have changed part of my formula on the original sheet from =if(N5=0,… to =if(N5=C16 or C116,…
And it is now working for both non duplicate and duplicate codes, while the file-sample I uploaded to the Forum continues to work with the condition ‘N5=0’
ALSO
I get the impression that if I start a spreadsheet file in Libre Office and only using Libre Office, saving it as ods, the universal formula works as expected. If one switches the spreadsheet file between Libre Office and MS Office problems are bound to occur.
Did anyone else have this experience?