https://bugs.documentfoundation.org/show_bug.cgi?id=124710
Bug ID: 124710
Summary: [1] IFS function does not use default result in
certain cases
Product: LibreOffice
Version: 6.1.5.2 release
Hardware: x86-64 (AMD64)
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: forou...@gmail.com
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.
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
