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.
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.
Created attachment 150728 [details] simple ifs example
(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
@Erack, I thought you might be interested in this issue...
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.
Shortest reproducer: =IFS( 0, NA(), 1+0, "no idea")
I will have a look at it and decide if I can make a fix.
(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.
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" )
Choosing the best Washing machine in India for your home can be a tough job. You have a variety of options with best Washing machine in India available in the market. The number of models can overwhelm you. https://washingradar.com/best-washing-machine/ https://bestwashingmachine.in
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.
(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.
Dear forouhcr, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug