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?

Answer 1

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.