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:
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)
String "no idea" - the default IFS result.
User Profile Reset: No
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 126.96.36.199
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..
=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".
=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.
=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
"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
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.
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.