Conditional branches of a function raise false circle reference errors

Question

I have a big function with lots of branches of IFS; each branch uses different references of the worksheet:

MYFUN = LAMBDA(i,
    IFS(
        i = 1, // a formula uses Row 1 for instance,
        i = 2, // a formula uses Row 2 for instance,
        ... ...
)

Then, I realize that if I write =MYFUN(2) at Cell C1, a circle reference error is raised, even though during runtime =MYFUN(2) does not use values in Row 1.

I try to reproduce the problem with a small code. I define a function as follows:

TRY = LAMBDA(i,
    IFS(
        i = 1, Sheet1!$B$2,
        i = 2, Sheet1!$D$2,
        TRUE, "haha"
    )
);

Then, writing =TRY(2) at Cell B2 returns well the value of D2 without the error of circle references, which is good.

Now, I add a SUM function as follows:

TRY = LAMBDA(i,
    IFS(
        i = 1, SUM(Sheet1!$B$2),
        i = 2, Sheet1!$D$2,
        TRUE, "haha"
    )
);

Now, writing =TRY(2) at Cell B2 raises a circle reference error, even though SUM(Sheet1!$B$2) does not need to be executed.

Does anyone know why the behaviour is like that?

How could I restructure the code like in MYFUN to avoid false circle reference errors?

PS:

I also realize that ROWS(Sheet1!$B$2) at the place of SUM(Sheet1!$B$2) does not raise circle reference error. So what's their semantics?

Putting just the IFS function in B2: `=IFS(2=1,SUM(B2),2=2,D2,TRUE,"HAHA")` will create the circular reference, so it is not the LAMBDA but the IFS that is causing it. — Scott Craner, May 11 '22 at 18:39
If you do the simple formula and evaluate it you see that it tries to resolve every criterion in order then returns the one that is associated with the first true. It is not like a nested IF that will only try to resolve the correct criterion. So all criterion will need to not cause that error. — Scott Craner, May 11 '22 at 18:44
Didn't know that IFS works like that. So that means also that IFS is costly. So using nested IF is a solution. I will leave the question open a little bit to see if there are other better rewritings. — SoftTimur, May 11 '22 at 18:47

score 3 · Answer 1 · answered May 11 '22 at 18:49

3

IFS will try to resolve every criterion then return the one associated with the first TRUE. It does not find the first TRUE then resolve the Criterion associated.

Put 6 in B2 and 3 in D2 then put this in B4

=IFS(2=1,SUM(B2),2=2,D2,TRUE,1=1)

Then with that cell selected evaluate the formula:

We can see that all 6 criteria were resolved, but the 3 was returned.

answered May 11 '22 at 18:49

Scott Craner

148,073
10
49
81

But why `=IFS(2=1,B2,2=2,D2,TRUE,1=1)` at B2 does not raise a circle reference error? – SoftTimur May 12 '22 at 12:09
If you put it in b2 it is trying to refer to itself. All criteria must resolve correctly or it will throw the error. – Scott Craner May 12 '22 at 12:53
what is odd is that it does not throw a circle reference error. – SoftTimur May 12 '22 at 12:55
hmmm, I guess since it is just the reference it does not do anything with it. See the final step in the photo. D2 is just a reference and not a value. My guess is that it does not actually return the value unless needed in another formula. – Scott Craner May 12 '22 at 19:10

Conditional branches of a function raise false circle reference errors

1 Answers1