Meaning logical Also called a boolean, it is one of several data types used in spreadsheets in applications such as Excel and Google Sheets. Named after the 19th century mathematician George Boole, boolean values are part of the branch of algebra known as Boolean algebra or boolean logic.
Boolean logic is vital to all computer technology, not just spreadsheet programs, and is based on the concept that all values can be reduced to WHERE or FALSE or since computer technology is based on the binary number system: a or 0 †
Remark † These instructions apply to versions of Excel 2019, 2016, 2013, 2010, and Excel for Office 365.
- Boolean Values and Boolean Spreadsheet Functions
- Booleans and Arithmetic Functions
- Convert TRUE and FALSE to 1 and 0
- Boolean Values and Excel Formulas
Boolean Values and Boolean Spreadsheet Functions
Boolean values in spreadsheet programs are usually created using a logical group of functions, such as function IF function AND and function OR † †
In these functions, booleans are an input source for one of the function’s arguments, or they can be the output or results of a function that evaluates other data in the worksheet.
For example, the first function argument IF in line 4 – boolean test argument – required to return a boolean value as an answer. The argument must always evaluate to a condition that can only lead to an answer WHERE or FALSE † Because:
If the argument returns an answer WHERE , the function performs one action. In this example, it multiplies the data in cell A2 at 25.
If the argument returns an answer FALSE , the function performs a different action. In this case it multiplies the data in cell A2 at 10.
Booleans and Arithmetic Functions
Unlike logic functions, most of the functions in Excel and Google Sheets are that perform arithmetic operations, such as: SUM † COUNT And AVERAGE ignore booleans when they are in cells contained in function arguments.
For example, in the example image, the COUNT in . function is line 5 which only counts cells containing numbers, ignores the boolean values TRUE and FALSE in the cells A3, A4 and A5 and returns the answer 0.
Convert TRUE and FALSE to 1 and 0
To include Boolean values in calculations of arithmetic functions, you must first convert them to numerical values before passing them to the function. Two ways to complete this step:
Multiply Boolean values by one (as shown by the formulas in lines 7 and 8 examples that multiply values WHERE And FALSE in cells A3 and A4 one by one).
Add zero to any boolean value (as represented by the formula in line 9 example adding 0 appreciate WHERE in cell A5 †
These operations result in the conversion of the value WHERE in cells A3 and A5 in a and values FALSE here cells A4 before 0 † As a result, the function COUNT in line 10 which summarizes the numerical data in cells A7 to A9 gives the result 3 but not 0 †
Boolean Values and Excel Formulas
Unlike arithmetic functions, formulas in Excel and Google Sheets that perform arithmetic operations such as addition and subtraction like to read Boolean values as numbers without the need for conversion. Such formulas are set automatically WHERE Directly a And FALSE Directly 0 †
As a result, the addition formula in line 6 in example image
= A3 + A4 + A5
reads data in three cells as:
= 1 + 0 + 1
and returns a comment 2 respectively.