Bug 124710 - IFS function erroneously propagates error results from functions within IFS (smallest reproducer in comment 6)
Summary: IFS function erroneously propagates error results from functions within IFS (...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.7.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2019-04-12 20:51 UTC by forouhcr
Modified: 2019-10-11 09:26 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Small spreadsheet showing IFS working and malfunctioning. (18.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-04-12 20:56 UTC, forouhcr
Details
simple ifs example (18.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-04-13 12:09 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description forouhcr 2019-04-12 20:51:22 UTC
Description:
I have an IFS function with 4 conditions, the final condition being a default result (i.e. if the previous 3 conditions are false, the final condition will be true and give the result). But the final default condition does not "fire" and the IFS returns #N/A.

Here's my formula:
=IFS(
     OR(I9="pago",I9="reci"),VLOOKUP(J9,$local_vlookup.A:C,2,0),
     AND(I9="tran",E9>0),"FT from",
     AND(I9="tran",E9<0),"FT to",
     1,"no idea"
    )
The result is #N/A.

I played with the formula by changing some conditions. When the VLOOKUP is replaced with a text value, the IFS formula works (gives "no idea"). So the problem is with VLOOKUP which returns #N/A (a valid value where there's no match) which causes IFS to break down rather than treat the condition as FALSE.

Note that the IFS above came from an Excel 2016 where it works.

Steps to Reproduce:
1.Enter formula given in summary in cell K9
2.Create a sheet called local_vlookup, put any values in columns A, B and C
3.Make sure the VLOOKUP does not find a match (i.e. result is #N/A)

Actual Results:
#N/A

Expected Results:
String "no idea" - the default IFS result.


Reproducible: Always


User Profile Reset: No



Additional Info:
IFS should treat a VLOOKUP #N/A result as a FALSE (as Excel 2016 does) rather than a formula error.

Please see attached ODS spreadsheet.
Comment 1 forouhcr 2019-04-12 20:56:27 UTC
Created attachment 150721 [details]
Small spreadsheet showing IFS working and malfunctioning.

In sheet 1 the highlighted #N/A values show where the IFS function mishandles a valid #N/A result from a VLOOKUP. You will also see three rows where the VLOOKUP found a match and the IFS formula worked.

Note that this spreadsheet was created in Excel 2016 where no such problem exists; i.e. the #N/A result in Excel is treated as a FALSE and the following conditions are evaluated until TRUE or the default is reached.
Comment 2 Oliver Brinzing 2019-04-13 12:09:33 UTC
Created attachment 150728 [details]
simple ifs example
Comment 3 Oliver Brinzing 2019-04-13 12:19:10 UTC
(In reply to forouhcr from comment #0)
> I played with the formula by changing some conditions. When the VLOOKUP is
> replaced with a text value, the IFS formula works.

reproducible with LO 5.4.7.2
it seems evaluting is stopped as soon as vlookup returns #N/A
Comment 4 Oliver Brinzing 2019-04-13 12:32:27 UTC
@Erack, I thought you might be interested in this issue...
Comment 5 Eike Rathke 2019-04-15 14:01:29 UTC
Seems to be an error in error propagation..

Same with

  =IFS( OR(I9="pago",I9="reci"), NA(), AND(I9="tran",E9>0),"FT from", AND(I9="tran",E9<0), "FT to", 1, "no idea")

but not with

  =IFS( 0, NA(), 0,"FT from", 0, "FT to", 1, "no idea")

where the result is "no idea".

Also

  =IFS( OR(I9="pago",I9="reci"), NA(), 0,"FT from", 1, "no idea")

produces the expected result, but not

  =IFS( OR(I9="pago",I9="reci"), NA(), 0+0,"FT from", 1, "no idea")

and also not

  =IFS( OR(I9="pago",I9="reci"), NA(), 0,"FT from", 1+0, "no idea")

So whenever an expression is to be evaluated for the condition after a not to be returned error result was encountered.
Comment 6 Eike Rathke 2019-04-15 14:02:38 UTC
Shortest reproducer:

  =IFS( 0, NA(), 1+0, "no idea")
Comment 7 Winfried Donkers (retired) 2019-04-16 11:10:19 UTC
I will have a look at it and decide if I can make a fix.
Comment 8 Winfried Donkers (retired) 2019-04-17 10:09:08 UTC
(In reply to Eike Rathke from comment #6)
> Shortest reproducer:
> 
>   =IFS( 0, NA(), 1+0, "no idea")

I know what happens, but not yet why.
The raw stacktype for the arguments in ScInterpreter::ScIfs_MS() is
0         svDouble,
NA()      svError,
1+0       svError,
"no idea" svString.
This is not influenced by reversing the stack.
ScInterpreter::ScIfs_MS() gives #N/A as result, which is not parameter NA(), but the result of PushNA(), because of svError (1+0) is not popped and evaluated. 

I saw that the parameter parser (I don't know its proper term) parses 1+0 into 1, but have not yet found out if/when the raw stacktype for parameter (1+0) changes into svError.

I keep on digging, but it may cost some time.
Comment 9 Winfried Donkers (retired) 2019-04-17 15:09:19 UTC
The cause is clear now (the 'error' NA() is propagated on the stack, causing incorrect results in certain cases), the solution too (redesign of the function to jump-type).
I will give it a try, it will probably take time.

A workaround is to avoid using parameters that can give errors as result, i.e. use references that have been tested not to contain an error value.
In the formula given in description, that would mean putting VLOOKUP(J9,$local_vlookup.A:C,2,0) in an unused cell, e.g. Z9, as follows:
IFERROR( VLOOKUP(J9,$local_vlookup.A:C,2,0), "ERROR OCCURRED:) and replace the formula by
=IFS(
     OR(I9="pago",I9="reci"),Z9,
     AND(I9="tran",E9>0),"FT from",
     AND(I9="tran",E9<0),"FT to",
     1,"no idea"
    )
Comment 10 sachin Khanna 2019-06-19 15:18:34 UTC Comment hidden (spam)
Comment 11 Xisco Faulí 2019-09-26 10:21:28 UTC
Dear Winfried Donkers,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.
Comment 12 Winfried Donkers (retired) 2019-09-26 10:48:00 UTC
(In reply to Xisco Faulí from comment #11)
> Dear Winfried Donkers,
> This bug has been in ASSIGNED status for more than 3 months without any
> activity. Resetting it to NEW.
> Please assigned it back to yourself if you're still working on this.

Changing the IFS function to a so called 'jump function' (like e.g. IF) that will not propagate error results from arguments not relevant to the end result is a complicated task. I still hope to succeed -with the help from Eike Rathke- but it will take time.
Developers who can do this change easily are welcome, currently I have a lot of coding problems to solve and Eike hasn't a proven solution either.
Changing the IFS function to a jump function is the only way to solve this bug report and similar problems with the function.