Our latest release supports the new formula function CHOOSE, which is an attractive alternative to the venerable IF function if you need to select a value based on an integer. The CHOOSE function is also supported by all popular spreadsheets.
The problem with the IF formula function
One of the most popular formula function offered by spreadsheets is the IF function. It enables you to return different values depending on the value of a logical value, called the condition and given as the first parameter. The second parameter is chosen if the condition is TRUE and the third parameter is chosen if the condition is FALSE. To test multiple values, the third parameter is typically set to another IF function, whose third parameter can be yet another IF function, and so on.
Here’s a formula demonstrating nested IF functions, which returns “one” if Field1 is equal to 1, “two” if it is equal to 2 and “three” if it is equal to 3:
(The third IF function has no third parameter, meaning that it returns a blank value if Field1 is not equal to 1, 2 or 3.)
A formula selecting a price using the IF formula function might look like this:
As you can imagine, writing such formulas quickly becomes tedious and error-prone. As all parentheses need to be closed, you’ll wind up with a large number of closing parentheses at the end of your formulas. Maintaining these formulas when you need to update your data is hard, monotonous work.
The new CHOOSE function
Let’s say you need to select a value based on an integer ranging from 1 to an arbitrarily large number. Also, let’s say that there are no gaps, meaning that you’re interested in all integers between 1 and the arbitrarily large number. In that context, the new CHOOSE function is a very attractive alternative to IF.
The first parameter to CHOOSE is a number which tells it which parameter to choose. If the first parameter is 1, the second parameter is chosen. If the first parameter is 2, the third parameter is chosen, etc.
The IF formula above returning “one”, “two” or “three” can be written using the CHOOSE formula function as follows:
The IF function remains far more flexible than the CHOOSE function, because it can use arbitrary logical conditions and CHOOSE is only able to select a parameter based on the number you supply as the first parameter. It turns out, though, that doing so is very common.
Selecting a value for a drop-down field with CHOOSE
What if you want to select a value based on what the user selects in a drop-down field with text values? Wouldn’t that require the use of the IF function?
Not anymore. Our new release introduces a new property for
drop-down fields, Index, which returns the position of
the value selected by your user. If your user selects the first value
from the drop-down field MyDropDownField, the formula
MyDropDownField.Index
returns 1, and if the drop-down field has 20 values and the user
selects the last value, that same formula returns 20.
You can use the Index property in conjunction with CHOOSE to quickly select data based on the value selected by your user. Here, again, is the IF-based formula selecting a price:
Here is the same formula, now using the CHOOSE formula function:
This version is clearly much shorter and far easier to maintain when you need to update your data. The only downside is that your formulas are now dependent on the order of your drop-down field values. If you change the ordering, you must update all formulas.
Another advantage to CHOOSE over IF is that CHOOSE should be faster, especially if you have hundreds or even thousands of values. IF needs to compare the value of the drop-down field to all the values you include in your comparisons one-by-one, whereas the CHOOSE function jumps straight to the correct value and returns it.
How many parameters can I pass to CHOOSE?
Spreadsheets typically limit the number of parameters you can pass to so-called variadic functions (which take an arbitrary number of parameters). Google Sheets supports the CHOOSE function but limits you to 29 parameters. Microsoft Excel is more generous and allows up to 254 parameters and LibreOffice allows for 30 parameters.
Calcapp actually enforces no limit. Your web browser probably does have a limit, though, but it’s very high — around 30,000 parameters should work at a minimum. We don’t suggest you try to fit that much data into your formulas, though.
We added the CHOOSE function partly to let you more easily convert spreadsheet data tables to formulas. You can read more about doing the conversion here and also watch a video of the process.