# Documentation/Calc Functions/SWITCH

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

## 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**argument.

*n***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**argument matches*n***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

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 **Boolean Value** option in the *Category* area of the *Numbers* tab, and click the 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:

## ODF standard:

None.

## Related (or similar) Excel functions:

SWITCH since v.2016.