Boolean Value (Logical Value) Definition and Use in Excel

Boolean Values Definition and Use in Excel and Google Spreadsheets

Boolean Value Definition and Use in Excel and Google Spreadsheets
Boolean Value Definition and Use in Excel and Google Spreadsheets. © Ted French

Boolean Value, sometimes referred to as a Logical Value, is one of several types of data used in Excel and Google Spreadsheets.

Named after the nineteenth-century mathematician George Boole, Boolean values are part of a branch of algebra known as Boolean algebra or Boolean logic.

Boolean logic is important to all of computer technology, not just spreadsheet programs, and rests on the concept that all values can be reduced to either TRUE or FALSE or since computer technology is based on the binary number system, to either 1 or 0.

Boolean Values and Spreadsheet Logical Functions

The use of Boolean values in spreadsheet programs is most often associated with the logical group of functions such as the IF function, the AND function, and the OR function.

In these functions, as shown in in the formulas in rows 2, 3 and 4 in the image above, Boolean values can be used as the input source for one of the function's arguments or they can form the output or results of a function that is evaluating other data in the worksheet.

For example, the first argument of the IF function in row 5 - the Logical_test argument - is required to return a Boolean value as an answer.

That is to say, the argument must always evaluate a condition that can only ever result in a TRUE or FALSE answer. And, as a result,

  • If the argument returns an answer of TRUE, the function will perform one action (multiply the data in cell A2 by 25).
  • If the argument returns an answer of FALSE, the function will perform a different action (multiply the data in cell A2 by 10).

    Boolean Values and Arithmetic Functions

    Unlike the logical functions, most functions in Excel and Google Spreadsheets that perform arithmetic operations - such as SUM, COUNT, and AVERAGE  - ignore Boolean values when they located in cells included in a function's arguments.

    For example, in the image above, the COUNT function in row 5, which only counts cells containing numbers, ignores the TRUE and FALSE Boolean values located in cells A3, A4, and A5 and returns an answer of 0.

    Converting TRUE and FALSE to 1 and 0

    To have Boolean values included in the calculations of arithmetic functions, they must first be converted to numeric values before passing them to the function. Two simple ways of accomplishing this step are to:

    1. multiply Boolean values by one - as shown by the formulas in rows 7 and 8, which multiply the values TRUE and FALSE in cells A3 and A4 by one;
    2. add zero to each Boolean value - as shown by the formula in row 9, which adds zero to the value TRUE in cell A5.

    These operations have the effect of converting:

    • the value TRUE in cells A3 and A5 to 1;
    • the value FALSE in cell A4 to 0.

    As a result, the COUNT function in row 10 - which totals number data in cells A7 to A9 - returns a result of three rather than zero.

    Boolean Values and Excel Formulas

    Unlike arithmetic functions, formulas in Excel and Google Spreadsheets that carry out arithmetic operations - such as addition or subtraction - are happy to read Boolean values as numbers without the need for conversion - such formulas automatically set TRUE equal to 1 and FALSE equal to 0.

    As a result, the addition formula in row 6 in the image above,

    = A3 + A4 + A5

    reads the data in the three cells as:

    = 1 + 0 + 1

    and returns an answer of 2 accordingly.