Forum Discussion
Will I save resource when adding an If function before complicated functions?
Hi! I just have a simple question.
Will I save resource by adding an IF function before doing complicated functions?
Or Excel will do all the functions first and return the result, no matter if the IF function is TRUE or not?
For example,
--------------------------------------
=IF(A1=TRUE,
BUNCH OF COMPLICATED FUNCTIONS
,0)
----------------------------------------
Will EXCEL just skip all the complicated functions and return "0" if A1 is FALSE?
Or EXCEL will do all the functions any way, and return "0" when A1 is FALSE?
Thank you for answering!
6 Replies
As Sergei mentioned, generally the answer is yes.
Two blog posts you might find useful are this one from Charles Williams:
Short-circuiting Excel Formulas: IF, CHOOSE, IFS and SWITCH | Excel and UDF Performance Stuff
And this one from me:
#The SWITCH and LET functions – Excel formula performance - flex your data
Yes, IF() evaluates expression for the first found (in case of nested IF) TRUE condition and skips the rest. If no TRUE is found it returns result for final FALSE. In opposite to IFS() or SWITCH() which evaluate expressions for every condition.
However, I believe that is not always true because I believe if you have dynamic arrays in the IF then the IF will act like the IFS and SWITCH and evaluate all conditions.
Not exactly. Let say we have formula
=IF(A1=TRUE, AVERAGE(SORT(UNIQUE(SEQUENCE(100000,500)))),0)It takes couple of seconds to generate result if in A1 is TRUE. If we change on FALSE, zero will be returned immediately.
However, if we remove AVERAGE() and formula returns big enough spill, changing TRUE on FALSE could take even more time than direct calculation - Excel cleans the grid removing that spill.
With
=IFS(A1=TRUE, AVERAGE(SORT(UNIQUE(SEQUENCE(100000,500)))),TRUE, 0)both TRUE and FALSE take about couple of seconds to return result.
