Forum Discussion
Will I save resource when adding an If function before complicated functions?
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.
Sorry let me clarify. If the conditional is an array. So if we tweak your example:
=IF(A1:A2=TRUE, AVERAGE(SORT(UNIQUE(SEQUENCE(100000,500)))),0)Even if BOTH A1&A2 are False it will still take a second or so to calculate
Oh, yes m_tarler​ , you are right. With array of conditions it first evaluates each expression and after that builds array of results depends on values in conditions array. With only exception of single element of conditions array, e.g. ={FALSE} in A1.