Short-circuiting Excel Formulas: IF, CHOOSE, IFS and SWITCH

What is formula short-circuiting?

Short-circuiting occurs in Excel formulas when an IF statement only evaluates the True part of the IF and skips the False part of the IF. For example in this formula:

=IF(1>2,SUM(A10:B1000),SUM(C10:D1000))

Because 1>2 evaluates to False the SUM(A10:B1000) calculation never gets evaluated – only the SUM(C10:D1000) calculation is done.
Short-circuiting with IF is useful to avoid doing slow calculations when they are not needed and to avoid creating errors.

Which Excel functions short-circuit?

As of January 2023 there are 4 Excel conditional functions that could short-circuit:

• IF
• CHOOSE
• IFS
• SWITCH

But of these 4 only IF and CHOOSE actually short-circuit – IFS and SWITCH do not short-circuit. We can test this using a VBA UDF that tells us when it gets executed.

Option Explicit

Function Pass(valu As Variant)
    Pass = valu
    Debug.Print valu
End Function

Every time that the Pass function gets called it will write the valu variable to the VBE Immediate window (VBE – Ctrl G).
So now we can write formulas using IFS, IF, CHOOSE and SWITCH to see how they work.

Clicking Ctrl-Alt-F9 forces Excel ro recalculate all these formulas and the Pass UDF will output its results to the Immediate window.
The results appear in what looks like reverse sequence because Excel calculates formulas in last-entered first-calculated sequence (until the sequence is altered).

The IFS formula returns 3 to the grid, but shows 1 2 3 4 5 in the Immediate window – every component of the IFS formula has been executed – IFS does not short-circuit.

The IF formula returns 10 to the grid and only shows 10 in the Immediate Window – only the pass(10) has been executed – IF does short-circuit.

The CHOOSE formula returns 12 to the the grid and only shows 12 in the Immediate window – only the pass(12) part has been executed – CHOOSE does short-circuit.

The SWITCH formula returns 22 to the grid, but shows 21 22 23 in the Immediate window – ecery component of the SWITCH formula has been executed – SWITCH does not short-circuit.

Why do CHOOSE and IF short-circuit but IFS and SWITCH do not?

Normally the Excel formula interpreter evaluates all the arguments of a function before passing them to the function.

So in order to short-circuit IF and CHOOSE I think the Excel formula interpreter must recognise IF and CHOOSE as a special type of interpreter native function and evaluate the choice-of-arguments logic before evaluating the arguments in the usual left-to-right sequence.

Some people would regards the IFS and SWITCH behaviour as a bug!