Documentation/Calc Functions/XOR

    From The Document Foundation Wiki
    Other languages:


    Function name:

    XOR

    Category:

    Logical

    Summary:

    Performs a logical "exclusive or" (XOR) operation on multiple conditions. The result is TRUE if and only if an odd number of the specified conditions are true; otherwise FALSE.

    Syntax:

    XOR(Logical Value 1 [; Logical Value 2 [; … [; Logical Value 255]]])

    Returns:

    Returns the logical value TRUE if an odd number of the supplied conditions are met. Returns the logical value FALSE when an even number of the supplied conditions are met.

    Arguments:

    Logical Value 1, Logical Value 2, … , Logical Value 255 give the set of conditions to be checked. Each condition should comprise an expression that evaluates to give a logical value that is either TRUE or FALSE. If an expression evaluates to a number instead of a logical value, then the value 0 (zero) is treated as FALSE and all non-zero numeric values are treated as TRUE. Each argument may take one of the following forms:

    • A logical value, or an expression that can be evaluated to a logical value. Examples include TRUE, 3, 1<5, 2+3=7, A2*A3, and B8<10.
    • A reference to a single cell containing a logical value or expression.
    • A reference to a cell range containing logical values or expressions (for example, D6:E12 or A1:B9~E12:E14).
    • The name of a named range, comprising cells containing logical values or expressions.
    • The name of a database range, comprising cells containing logical values or expressions.
    • An inline array of logical values or numbers (for example {TRUE, FALSE, 0, 123.456}).

    Note that although XOR can accept up to 255 arguments, each argument could specify a range of cells. This means that the number of conditions processed could be many more than 255.

    • If the arguments do not give at least one logical value, then XOR reports a #VALUE! error.
    • If the arguments give exactly one value, then XOR returns that logical value.
    • If any argument is a string in quotation marks, then XOR reports a #VALUE! error. This does not apply to references to cells containing strings.
    • Empty cells and cells that contain text are ignored by XOR.
    • If a cell contains an error value, then XOR propagates that error irrespective of the content of other cells.

    Additional details:

    Details specific to XOR function

    • In its simplest form, the logical XOR (sometimes known as exclusive OR, or exclusive disjunction) of two logical values is an operation that produces a value of TRUE if exactly one of its operands is true; otherwise the result is FALSE. It can be expressed as a truth table as follows:
    Truth table for logical XOR operation
    Operand 1 Operand 2 Operand 1
    XOR
    Operand 2
    true true false
    true false true
    false true true
    false false false
    • XOR ignores any text in cells or empty cells. If you suspect wrong results, look for text within the supplied data range(s). To highlight text content within a data range, use Calc's View ▸ Value Highlighting feature.
    • XOR is available since LibreOffice 4.0 and it can be exported to XLSX (2013) format since LibreOffice 4.1. 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:

    Data for examples
    A B C D
    1 =TRUE() 23.45 2.2
    2 =NOT(FALSE()) 3
    3 1 -5.4
    4 2
    5 3 "Conditions": A1:A5   named range
    Formula Description Returns
    =XOR(TRUE()) The only argument is true. TRUE
    =XOR(TRUE(); TRUE()) Both arguments are true. FALSE
    =XOR(TRUE(); C1>10; FALSE()) Two of the arguments are true. FALSE
    =XOR(D1:D3) All three supplied numeric values are not equal to 0. TRUE
    =XOR({2; 4; 6; 8}) All four supplied numeric values are not equal to 0. FALSE
    =XOR(12<13; 14>12; 7<6) The condition given in the third argument is false while the first two are true. FALSE
    =XOR(Conditions) All five cells in the named range are either TRUE or not 0 (zero). TRUE

    Related LibreOffice functions:

    AND

    FALSE

    IF

    IFERROR

    IFNA

    IFS

    NOT

    OR

    SWITCH

    TRUE

    ODF standard:

    Section 6.15.10, part 2

    Related (or similar) Excel functions:

    XOR since v.2013.