Using multiple conditions with the IF function has traditionally required writing lots of parentheses. Here’s a formula which returns 10 if the value of Field1 is 1, 20 if it is 2, 30 if it is 3 and 99 otherwise:
If you need a large number of conditions, you need to write a great many end parentheses, which gets old fast.
Microsoft introduced the IFS function a few years ago, which only requires two parentheses. You’ll be happy to know that we now fully support this function.
This IFS formula is equivalent to the formula above:
IFS doesn’t have true support for a fallback value, meaning that you need to use the condition TRUE to include a fallback value.
We wanted to have full support for a fallback value, and we saw no need to use a separate function to do so. We simply extended IF, meaning that this formula works just as well:
We consider IFS to be a compatibility function. If you copy and paste formulas straight from Excel or need to copy formulas from Calcapp to Excel, by all means, use it. If not, we recommend that you stick with IF and simply leave out all the extra parentheses.
SWITCH
When Microsoft introduced IFS, they also introduced SWITCH, which is perfect when you need to test the same value for equality against other values. Here is a formula using SWITCH, which is equivalent to the other formulas above:
Finally, don’t forget about CHOOSE, which we have supported since 2018. If you only need to test for equality against a sequence of numbers starting with 1 (2, 3, 4, …), CHOOSE is perfect.
CHOOSE has no support for a fallback value, but this formula is in all other respects identical to the others on this page:
We added support for CHOOSE partly to enable drop-down field values to easily map to table values. We wrote an app to generate CHOOSE formulas from table data. That technique still works well, but you can also use the new XLOOKUP function along with a new app we wrote to do the conversion.
IFBLANK
Spreadsheets provide a number of convenient functions for working with values which may be errors. ISERROR is a straight-forward function which returns whether a value is an error.
This formula returns TRUE, as numbers cannot be divided by zero:
If you want to hide an error value, by using a different value if there is an error, using ISERROR is somewhat awkward:
The formula above uses the value derived from dividing the value of Field1 by zero, but only if it doesn’t lead to an error. (Obviously, this example is just for illustrative purposes, as we know for a fact that dividing a number by zero will always produce an error, so there’s no need to check.)
You’ll note that we need to repeat the calculation in the formula above. We could introduce a hidden field to avoid this, but using IFERROR is even better:
The formula above is fully equivalent to the IF formula above — and a lot shorter.
We liked using IFERROR so much that we wanted a version that would do for blank values what IFERROR does for errors. Enter IFBLANK.
Just like the name suggests, IFBLANK returns the first parameter unchanged if it is not blank, and returns the second parameter otherwise:
Normally, operators like + treat blank values as though they were the value 0. With IFBLANK, we can easily change that behavior, and make a blank value appear as though it was the value 10, with one easy-to-use function.