Using Function IFS and CURRENT within Test expressions leads to not comprehensible results.
Target/background: A cell (referencing whatever, e.g. COUNTIF etc.) should have a STYLE() applied, depending on another cell's value, e.g. a treshold in a real life example
Works with nested if, does NOT WORK with IFS. Example uploaded.
Steps to Reproduce:
All in uploaded "SampleSTYLE.ods":
Both ref value B2 and Treshold value B3 are the same, e.g. 4
leads to 4 with "Rot" = RED, wrong as CURRENT()=B3 is TRUE!
leads to 4 with "Gelb" = YELLOW, correct.
IFS should be able to work in combination with CURRENT
User Profile Reset: No
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Created attachment 135441 [details]
SampleSTYLE.ods, sample ODS explaining the IFS bug
LO calc version is officially shipped version with OpenSuse Leap 42.3
Asked this question before opening this bug: https://ask.libreoffice.org/en/question/120323/calc-bug-inbecause-of-function-style-german-vorlage/
Please read the help
"This function returns the result to date of evaluating the formula of which it is a part (in other words the result as far as that evaluation has got)."
What should be for you the value of CURRENT() in any place.
I've read the help and I'm not sure if I understand your remark.
In my example ODS both B5 and B6 are getting the same value B2 + STYLE(...), so in fact B2. Only difference is, that the same conditions
are resolved differently if they're resolved for one IFS() call or nested IF() calls.
not sure if I've got your comment "What should be for you the value of CURRENT() in any place." - Isn't "any place" both IFS and nested IF?
Created attachment 135474 [details]
Variant in position of CURRENT() in the IFS
I expect, that the result of CURRENT() is only changed, after the IFS was completely evaluated. It seems, that CURRENT() gets an internal values of the condition, that was not met.
Same here, in Version: 18.104.22.168
=0+IFS( […],CURRENT(),[…],CURRENT(),[…],CURRENT(), CURRENT()=1,"[yes!]",[…],CURRENT())
shows 1 no matter how the pairs are shuffled, unless you put "CURRENT()=1" first, in which case it's "Err:518"
Conditions using CURRENT() don't work properly... and if the comparison gets to the pair
it returns "1>2", so it changes.
IFS tries to return TRUE unless some type is given to it:
Thus most likely CURRENT() is set to TRUE (which turns into 1 if used in a number type formula) when the condition is met.
Inside the chosen branch it resets (!):
the former returns "before1z", the latter "beforezz".
Also, if no condition is met, the returned default value is "N/A"? Not sure it's a good idea.
Condition "ISNA(CURRENT())" doesn't catch anything either, BTW.
Well, what do you expect? CURRENT() returns the current internal value at the position of the evaluation chain where it occurs. When used multiple times within arguments to the same function the result depends on the order of evaluation. IFS() evaluates conditions until it finds a matching one, sprinkling CURRENT() all over makes it depend on internal behaviour what arguments will be evaluated first, which currently may be all before IFS() is called and in future may be only the ones until one is matched.
depends on whether the parameters are evaluated from left to right (usual but not necessarily) or from right to left. If from left to right then the second CURRENT() returns the result of the comparison CURRENT()>2, whatever CURRENT() might had been there, so 0 or 1.
is logical behaviour. In the first example CURRENT() returns the result of the condition 0=0, which is 1. In the second example it returns the "z" that was just pushed as operand for the & operator.
Using CURRENT() multiple times at the same function level within one expression means relying on internal implementation details. One should not. It may change without notice.
=ISNA(CURRENT()) returns FALSE because CURRENT() as first to be evaluated function does not return NA(), it's Err:518, =ISERROR(CURRENT()) returns TRUE.
(In reply to Eike Rathke from comment #8)
> IFS() evaluates conditions until it finds a matching one,
> sprinkling CURRENT() all over makes it depend on internal behaviour what
> arguments will be evaluated first, which currently may be all before IFS()
> is called and in future may be only the ones until one is matched.
As I opened this bug, I would be thankful if this bug is not going to be closed because someone else's examples or additions might be misleading or independent of my bug.
If you look on my example attached (https://bugs.documentfoundation.org/attachment.cgi?id=135441), the IFS formula in B5 is:
And my question is: For B3 constantly being e.g. "4", how could a value of "4" for B2 lead to the condition "CURRENT()>B3" being selected by IFS() ???
--> As ONLY the condition "CURRENT()=B3" is applicable, IMHO it does not have any impact if IFS() evaluates sub-formulas from left to right, or vice versa.
In the attached example LO shows a value of "4" for B5, so for whatever reason CURRENT might return any other value than "4", and as B3 is also "4", how could ever the condition "CURRENT()>B3" evaluated as true ??
I'm repeating myself:
"IFS() evaluates conditions until it finds a matching one, sprinkling CURRENT() all over makes it depend on internal behaviour what arguments will be evaluated first, which currently may be all before IFS() is called and in future may be only the ones until one is matched."
"Using CURRENT() multiple times at the same function level within one expression means relying on internal implementation details. One should not. It may change without notice."
> And my question is: For B3 constantly being e.g. "4", how could a value of
> "4" for B2 lead to the condition "CURRENT()>B3" being selected by IFS() ???
Because here the state of CURRENT is not what you think it is. For the first CURRENT()<B3 it is B2 that was previously pushed to the stack, for CURRENT()=B3 it is "Grün" that was just pushed on the stack, for CURRENT()>B3 it is "Gelb" that was just pushed on the stack, and a string is greater than a numeric value.
Anyway, I'll keep this and close bug 113595 instead, they have the same reason.
IFS() and SWITCH() currently evaluate all parameters' arguments in order encountered from left to right for IFS() and right to left for SWITCH() and return the result matching for the condition(s). They do not short-cut evaluation like IF() and CHOOSE() do. Hence for a function that has side effects like STYLE() all styles are applied and the last one evaluated wins.
*** Bug 113595 has been marked as a duplicate of this bug. ***
** Please read this message in its entirety before responding **
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 http://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://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
Still in 6.4.0 RC1. Both
@So: u're right, bug, evaluating things that shouldn't ...
while that's wrong OP question not solveable ...
*** Bug 137135 has been marked as a duplicate of this bug. ***
First try to solve a bug. I'm trying to find where those functions are defined in the source code. Somebody already look for it ?
Might find it here: master/sc/source/core/data/funcdesc.cxx
The problem here ist that the calculations within the IFS function (for instance the DIV) are evaluated before the actual IFS function itself. Hence, any further calculation leads to a DIV to ZERO error, because if there is an error, no further calculations are made.
So this example leads to this error:
This example works:
So I think the only possibility to get this to work, is to change the generated compilation steps for this function.