IFS is not the same as nested IF functions

According to the Office support site, “IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.”.

Although that is sort of true, you certainly can’t say that IFS works the same way as nested IF conditions. The real beauty of an IF formula, is that it only evaluates two parts – the first expression, and then either the value if true, or the value if false, but never both.

IFS, on the other hand, appears to evaluate everything you pass to it. It does not just evaluate each criterion until it finds a true one, and then calculate the related value and exit. It evaluates all the criteria, and all the related values, which is obviously not great if you are using complicated formulas for criteria, or results, or both.

In other words, this:

=IF(A1=””,””,IF(A1=1,complex_formula,IF(A1=2,another_complex_formula,third_complex_formula)))

is much more efficient than the ‘equivalent’ IFS version:

=IFS(A1=””,””,A1=1,complex_formula,A1=2,another_complex_formula,TRUE,third_complex_formula)

Sure, the latter might be a little easier to read (though I’m not even convinced about that personally), but it will always evaluate all of those formulas, even if A1 is empty. I’d suggest IFS should be employed sparingly, if at all.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.