does IFS() calculate the redundant terms in background

[thegurumonkey]

to save wasted processing I sometimes use

=if(A1="","", [heavy formula])

which clearly works, as the heavy formula isn't calculated when its redundant.

But if i use IFS() to do the same (as per the below)

=IFS(A1="", "", A1=0, "", 1=1, [heavy formula])

it should do the same... BUT when using the evaluate formulae button, the later supposedly-redundant terms do get calculated

So my questions is, is this calculation of redundant terms peculiar to the 'evaluate formulae' window, or does the processor do this extra work too, meaning i should avoids IFS() in favour of nested IF() or OR() in such cases... especially for avoiding volatile formulae and/or big sheets.

Many many thanks!
Mike

 

[RoryA]

As far as I can tell, IFS does not have the same short-circuiting as IF unfortunately. Doing a quick test with a UDF, it evaluates all the criteria and all the values too.

 

[joeu2004]

The Evaluate Formula operation might be misleading you, or you are misinterpreting what it shows you.

The evaluation should proceed from left to right, and it should stop on the first TRUE condition. And it should evaluate only the TRUE value-if-true expression.

The IFS support webpage states: ``IFS can take the place of multiple nested IF statements``. Since left-to-right and true-only processing is an essential feature of nested IF functions, I would be surprised if IFS cannot, indeed, "take the place" of nested IFs.

My version of Excel does not support IFS. So I cannot confirm that.

You can confirm the left-to-right processing with something like the following:

=IFS(FALSE, myudf(1), FALSE, myudf(2), TRUE, myudf(3), FALSE, myudf(4), TRUE, myudf(5))

where myudf is the following VBA function, entered into a new VBA module (right-click worksheet tab, click View Code > Insert Modlule):

Function myudf(x)
MsgBox "myudf " & x
End Function

You should see only a msgbox with the value 3.

To re-execute the statement, select the cell, press f2, then press Enter.

 

[joeu2004]

I'm pressed for time. A better test would be to use myudf(x)+rand()>1 for FALSE and myudf(x)+rand()<1 for TRUE, in addition to the myudf calls that I showed. Use a different value for each "x". And now, we expect __two__ calls to myudf; ergo, __two__ msgboxes.

 

[joeu2004]

I'm pressed for time. [....] And now, we expect __two__ calls to myudf

Sorry, brain fart! Please try the following:

=IFS(myudf(11)+rand()>1, myudf(1), myudf(12)+rand()>1, myudf(2), myudf(13)+rand()<1, myudf(3), myudf(14)+rand()>1, myudf(4), myudf(15)+rand()<1, myudf(5))

Note that myudf, as I wrote it, always returns zero, and rand()<1 is always true.

So, I expect only the highlighted expressions to be evaluated. We should see 4 msgboxes with the numbers 11, 12, 13 and 3.

Is that what you see?

It would be helpful to me to know. If I'm wrong, I would __never__ suggest using IFS, since 99% of my use of nested IFs depends on the left-to-right and true-only evaluation.

-----

But it seems that RoryA did exactly that test -- our initial postings crisscrossed on the wire -- and he says my expectations are wrong. Yikes!

Rory, did you really use Excel's IFS function, or did you use your own VBA implementation of IFS in a version of Excel that does not support IFS?

 

[RoryA]

Rory, did you really use Excel's IFS function, or did you use your own VBA implementation of IFS in a version of Excel that does not support IFS?

The former. What would have been the point of the latter?

 

[thegurumonkey]

Thanks for your time. I'm that not familiar with UDFs, but I'm hearing that IF() seems the safer way to go. I'm certainly not hearing a clear case for efficiency savings by using IFS(), apart from during typing, which is a very small concern.

The simple tests I ran were:
both IF() and IFS() are not suceptible to errors in the redundant terms
e.g.
= IF(1=1,1, IF(A1=1,1,0) )
=IFS(1=1,1, A1=1,1)
both return 1 even if A1 returns an error

and in a more complicated test I found that actually a volatile function in the redundant term of both IF() and IFS() render the whole cell volatile...

so I'll keep on defaulting to IF(), and use IFS() only where it makes parsing complex formulae easier.
The IFX() suggestion on the excel dev uservoice is the efficiency saving I'm most waiting for anyway!

Huge thanks for your time! and sorry I failed to test your UDFs. If anyone knows how and has the will, please do put results up here for posterity.

 

[RoryA]

If you use Joe’s UDF you will see a message box for every function call in the formula.

 

[joeu2004]

I'm hearing that IF() seems the safer way to go. I'm certainly not hearing a clear case for efficiency savings by using IFS(), apart from during typing, which is a very small concern.

I agree entirely.

And I would be relunctant to use the new SWITCH function without similar testing, for fear that MSFT was equally remiss in its implementation.

sorry I failed to test your UDFs. If anyone knows how and has the will, please do put results up here for posterity.

Rory should have posted his. Someone in another forum did my experiment, to wit:

=IFS(myudf(11)+RAND()>1, myudf(1)/0, myudf(12)+RAND()>1, myudf(2),
myudf(13)+RAND()<1, myudf(3), myudf(14)+RAND()>1, myudf(4),
myudf(15)+RAND()<1, myudf(5))

Note that I added another aspect to the test, adding a purposeful error (div/0) in an expression associated with a false condition. In hindisight, I wish I had also changed myudf(14) to myudf(14)/0, adding a purposeful error to a false conditional expression after the first true condition.

The formula returns zero, as it should, indicating that the purposeful error does not abort the IFS series, as it shouldn't.

But the output in the VBA Immediate Window (substituting Debug.Print for MsgBox) is:

myudf 11
myudf 1
myudf 12
myudf 2
myudf 13
myudf 3
myudf 14
myudf 4
myudf 15
myudf 5

That demonstrates that all expressions are indeed evaluated, as Rory said.

In contrast, the equivalent IF formula is:

=IF(myudf(11)+RAND()>1, myudf(1)/0, IF(myudf(12)+RAND()>1, myudf(2),
IF(myudf(13)+RAND()<1, myudf(3), IF(myudf(14)+RAND()>1, myudf(4),
IF(myudf(15)+RAND()<1, myudf(5))))))

Again, the formula returns zero, and the VBA output is:

myudf 11
myudf 12
myudf 13
myudf 3

That demonstrates the left-to-right and only-value-if-true evaluation that we often depend on in "functions" (really operators) like IF and CHOOSE.