Documentation/Calc Functions/SWITCH

    From The Document Foundation Wiki

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    Function name:

    SWITCH

    Category:

    Logical

    Summary:

    Compares a supplied expression against a set of expected values and returns the result corresponding to the first matching value. If there is no match, a default result is returned if supplied.

    Syntax:

    SWITCH(Expression; Value 1; Result 1 [; Value 2; Result 2][; ... ; [Value 127; Result 127][; Default Result]])

    Returns:

    Returns a value whose type depends on the nature of the selected result argument.

    Arguments:

    Expression is an expression, or a reference to a cell containing an expression, that evaluates to a value that is to be compared to the Value n arguments.

    Value 1, Value 2, ..., Value 127 are expressions, or references to cells containing expressions, that evaluate to values to be compared against Expression. Each Value n argument must have a corresponding Result n argument.

    Result 1, Result 2, ..., Result 127 are expressions, or references to cells containing expressions, that give the results to be returned when a corresponding Value n argument matches Expression.

    Default Result is an expression, or a reference to a cell containing an expression, that gives the result to be returned if no Value n argument matches Expression.

    • If you choose not to specify a default result, 127 value / result pairs may be entered as parameters. If you choose to include a Default Result at the end of the list of parameters, then only 126 value / result pairs may be entered.
    • If no Value n argument matches Expression and no Default Result argument is supplied, SWITCH reports a #N/A error.

    Additional details:

    Details specific to the SWITCH function

    • The SWITCH function can often be used as an easier-to-read and more efficient alternative to nested IF functions.
    • SWITCH is available since LibreOffice 5.2. Take care therefore if you need compatibility.
    • This function is not part of the Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format standard. The name space is COM.MICROSOFT.SWITCH. Take care therefore if you need compatibility.


    General information about Calc's logical functions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing Calc’s logical functions.

    Calc uses the numbers 0 (FALSE) and 1 (TRUE) to represent logical values. For example, enter 1 in a cell that has the default general number format. Then select Format ▸ Cells on the Menu bar to display the Format Cells dialog, select the Boolean Value option in the Category area of the Numbers tab, and click the OK button. Notice that the number 1 is now displayed as TRUE. To revert to displaying the number 1, repeat the process but select Number instead of Boolean Value.

    To enter a logical value into a cell, simply type either TRUE or FALSE (case-insensitive) into the cell. Calc will recognize the logical value, display it in uppercase letters, and change the cell's format to that appropriate to Boolean values.

    Calc functions that produce a logical result return a number, either 0 or 1. In the case of a formula that simply calls such a logical function and is in a cell that has the default general number format, the cell is switched to the Boolean value format and the returned value is displayed as FALSE or TRUE. For example =TRUE() returns the value 1, which is displayed as TRUE; if you then change the format to a numerical format, it is displayed as the number 1.

    Calc functions that test for a logical result always evaluate the expression to be tested and check if it is equal to 0. The value 0 is taken as FALSE and any other value is taken as TRUE. For example, =NOT(0) returns TRUE and =NOT(57.89) returns FALSE.

    In contrast to Calc, Microsoft Excel has a separate type for logical values - they are not numbers but are sometimes converted to numbers. Take care therefore if you need compatibility.

    Examples:

    Basic example

    Suppose cell A1 contains an arbitrary date. The following formula extracts the month value from that date as an integer, and then converts the number to a string, giving a three-character abbreviation of the month.

    =SWITCH(MONTH(A1); 1; "Jan" ; 2; "Feb" ; 3; "Mar" ; 4; "Apr" ; 5; "May" ; 6; "Jun" ; 7; "Jul" ; 8; "Aug" ; 9; "Sep" ; 10; "Oct" ; 11; "Nov" ; 12; "Dec")

    • If A1 contains the date 1965-02-02, then the formula returns "Feb".
    • If A1 contains the number 44528, then the formula returns "Nov". 44528 is the date-time serial number that corresponds to 2021-11-28, assuming the default day 0 of 1899-12-30 is applied.

    Greater use of cell references

    Suppose a local shop sells small, medium, large, and extra-large pizzas. The prices of these pizzas are $9.49, $10.99, $12.99, and $14.98 respectively.

    Four customers purchase pizzas – Aidan (extra-large x 4), Jimmy (large x 3), Lara (medium x 2), and Natalie (small x 1).

    How much does each customer pay in total?

    Arrange the known data in a spreadsheet, as in the following table. The SWITCH function can be used in cells D9:D12 to calculate the amount owed by each person.

    A B C D
    1 Menu
    2 Size Price
    3 S $9.49
    4 M $10.99
    5 L $12.99
    6 XL $14.98
    7
    8 Customer Size Required Quantity Total Cost
    9 Aidan XL 4 $59.92
    10 Jimmy L 3 $38.97
    11 Lara M 2 $21.98
    12 Natalie S 1 $9.49
    • Cell D9 contains the formula
      =C9*SWITCH(B9; $A$3; $B$3  ; $A$4; $B$4  ; $A$5; $B$5  ; $A$6; $B$6).
    • Cell D10 contains the formula
      =C10*SWITCH(B10; $A$3; $B$3  ; $A$4; $B$4  ; $A$5; $B$5  ; $A$6; $B$6).
    • Cells D11 and D12 follow the same pattern.

    More complex conditions

    Consider the data in the following table.

    The data in cells A2:C7 defines the grading bands for a particular exam, in terms of the low and high exam scores for each band (as percentages).

    Seven students (cells A10:A16) sat the exam and their scores appear in cells B10:B16. Unfortunately, two obvious data entry errors have occurred in the exam scores!

    The SWITCH function can be used in cells C10:C16 to determine to which grade band each person's exam score belongs.

    A B C
    1 Grading bands
    2 Low score High score Grade band
    3 0 40 5
    4 40 50 4
    5 50 60 3
    6 60 70 2
    7 70 100 1
    8
    9 Student Exam score Grade band
    10 Angelina -10 Invalid score
    11 Annie 35 5
    12 Carl 45 4
    13 Hubert 55 3
    14 Lula 65 2
    15 May 75 1
    16 Winston 110 Invalid score
    • The following formula is entered into cell C10:
    =SWITCH(TRUE(); 
 AND(B10>=$A$3; B10<$B$3); $C$3; 
 AND(B10>=$A$4; B10<$B$4); $C$4; 
 AND(B10>=$A$5; B10<$B$5); $C$5; 
 AND(B10>=$A$6; B10<$B$6); $C$6; 
 AND(B10>=$A$7; B10<=$B$7); $C$7; "Invalid score")
    • A similar formula is entered in cells C11:C16, but replacing the above references to B10 by references to B11, B12, B13, B14, B15, and B16 respectively.

    Note that the data entry errors in the exam scores for Angelina and Winston manifest themselves with a grade band of "Invalid score". This is achieved by setting the Default Result argument to the string "Invalid score".

    Related LibreOffice functions:

    AND

    CONCAT

    FALSE

    IF

    IFERROR

    IFNA

    IFS

    MAXIFS

    MINIFS

    NOT

    OR

    TEXTJOIN

    TRUE

    XOR

    ODF standard:

    None.

    Related (or similar) Excel functions:

    SWITCH since v.2016.


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/SWITCH&oldid=707597"