Documentation/Calc Functions/CHOOSE

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



Function name:

CHOOSE

Category:

Spreadsheet

Summary:

Accepts a list of up to 30 values, returning one of them. The value to return is selected using an additional index argument.

Syntax:

CHOOSE(Index; Value 1 [; Value 2 [; ... [; Value 30]]])

Returns:

Returns one of the supplied values, selected using the given index. The type of value returned depends on the type of the indexed argument.

Arguments:

Index is an integer in the range 1 ≤ Index ≤ 30, or a reference to a cell containing such an integer, that determines which value is to be selected from the list and returned. If Index is 1, the function returns the first value in the list; if Index is 2, it returns the second value in the list; and so on.

Value 1, Value 2, ..., Value 30 are values from which one is to be selected and returned. Each may be text, a number (real or integer), a date, a logical value, a reference, or a formula.

  • If Index is non-numeric, then CHOOSE reports a #VALUE! error.
  • If Index is a non-integer value, then CHOOSE truncates the value to an integer.
  • If, after truncation, Index is less than 1, or greater than the number of values supplied, then CHOOSE reports an invalid argument error (Err:502).
  • If more than 30 Value n arguments are supplied, then CHOOSE reports a parameter list error (Err:504).

Additional details:

  • Providing that separators are present, values can be omitted from within the list. CHOOSE returns 0 if such a missing value is indexed. For example, the formula =CHOOSE(2; "str1"; ; "str3") returns 0.
  • CHOOSE can be used in an array formula. For example, select cells A1 to A6 and enter the array formula {=CHOOSE(1; D1:D6)} in the selected cells (using Ctrl+Shift+Enter rather than just Enter). Cell A1 assumes the value in D1, cell A2 assumes the value in D2, and so on.
  • Arguments other than the one chosen are not calculated or evaluated for side effects. For example, the formula =CHOOSE(1; 2; SQRT(-1)) returns the value 2, but the formula =CHOOSE(2; 2; SQRT(-1)) gives an an invalid argument error (Err:502) showing that the SQRT operation is only attempted when that argument is indexed.

Examples:

Formula Description Returns
=CHOOSE(1.34; "Base"; "Calc"; "Writer") Here the function truncates the value 1.34 and returns the first entry in the list of possible return values. Base
=CHOOSE(D1; B1; B2; B3; B4) where cells B1 to B4 contain 1.234, 2022-02-17, TRUE(), and SQRT(64) respectively. Cell D1 contains the number 2. Here the function indexes the second entry in the list of possible return values. It is equivalent to =B2. 2022-02-17
=SUM(CHOOSE(1; A7:A9; B12)) where cells A7, A8, and A9 contain the numbers 7, 13, and 19 respectively. Here the formula returns the sum of the numbers in the range A7:A9. It is possible to include a cell range as one of the values but this needs to be in the correct context (=CHOOSE(1; A7:A9; B12) gives a #VALUE! error). 39
=CHOOSE(A1; B1; B2; B3; "Today"; "Yesterday"; "Tomorrow") where cell A1 contains 2 and cell B2 contains the formula =PI(). Here the function indexes the second entry in the list of possible return values and returns the approximate value of π. If the value in cell A1 is changed to 4, then the text "Today" is returned instead. 3.14159265358979

Related LibreOffice functions:

IF

ODF standard:

Section 6.14.3, part 2

Equivalent Excel functions:

CHOOSE