Specifying an Array With The Choose Function

Use the Choose function to specify an array of values and select from it all at once in one statement. The function Choose(index, choice!, ciioice2,…choiceN) is listed in the functions pane of the formula editor un­der Programming Shortcuts.

This function use* the index value to choose from the values.

•   The range of index values (choiccl, choicc2, choicc3, etc.) depends on the number of choices. (You could not have 20 as the index value with only 10 choices),

•   All choices must be of one of the simple data types (Number, Currency, String, Boolean, Date, Time or DateTime) or a range type (Number Range, Currency Range, String Range, Date Range, Time Range or DateTime Range).

•   The choice field cannot be an array.

The following formulas demonstrate the Choose function. The first formula is “choosing” the third value (or choice) from the three choices available.

The second formula is ‘choosing” the first value from the available choices,

which is 12.

Choose (3. “good’-, ‘better’, “best”) //returns best Choose (1. 12. 24. 36. 48) //returns 12

Replacing a Select Case Statement with the Switch function

The Switch function evaluates the expressions from left to right, and returns the value associated with the first expression to evaluate to True.

The function Switch(Exprl, valuel, expr2,valuc2,…exprN, value N) is found in the functions pane of the Formula Editor under Programming Shortcuts.

Switch consists of pairs of expressions and values—Exprl, valuel, expr2, valuc2, etc. • •

• Ifexprl is True, valuel is returned.

• Ifexprl is FaUe and expr2 is True, then value2 is returned.

” Ifexprl and expr2 are False and expr3 is True, then value3 is returned.

•   If all of the expressions are False, then a default value is returned. (The default value returned depends on the type of the values in the value list. For example, if the values are of Number type, the default value is 0 and if the values are of String type, the default value is the empty string (“”).)

For example, the following formula evaluates if exprl is true; if yes, then value is returned. If no, then expr2 is evaluated. If expr2 is true, then value2 is returned, and so on.


Switch ((Orders. Order Amount! > 10000, ‘Excellent Sales”. Orders. Order Amount} > 5000, ‘Good Sales”. True, ‘Below Goal Sales”)

About Author

Leave A Reply