Boolean values

In case you’re not already familiar with the term, Boolean values are true and false. They can also be represented as yes/no, on/off, or 1/0. There’s also Boolean searching, which is based on Boolean logic, but that’s a post all by itself. Suffice it to say that when you’re doing Boolean logic you’re applying rules that will get you a Boolean value as a result. Simplistically, when you do a search on the Internet the search engine goes through its list of websites and checks to see if each one matches your search. If it does, that match is “true” and the site gets added to your list of results. If it doesn’t match then the test returns “false”, and you never see the site it was testing. Obviously it’s more complicated than that, and tries to judge how well sites match and give you the best ones first, but the root of it all is just Boolean logic.

Generally, if you want a Boolean value in a spreadsheet you have to either type true or false, or use a function that returns true or false. No matter how you capitalize it, it will convert it to TRUE or FALSE. Do not put quotes around it or before it, and if you misspell it or have a space after it or anything like that, it won’t work.

However, even though it will display TRUE or FALSE, Boolean values in spreadsheets are (sort of) numbers. You can add, subtract, divide, and multiply them with each other or with other numbers. False has a value of zero, and true has a value of 1. This means that TRUE+TRUE=2, TRUE-FALSE=1, and TRUE*FALSE=0. And when I say TRUE-FALSE=1 I do mean that it will return the number 1. In most programs it won’t be formatted as a Boolean value anymore, and there’s no way to force it to look like a Boolean value.

This is because most programs don’t have Boolean as a number format. Of the ones I tested only OpenOffice Calc does. In OpenOffice, if you format a number as Boolean then 0 is false and anything else is true. This means that TRUE-TRUE=TRUE and TRUE-TRUE=FALSE could both be true, since 2-1=1 and 1-1=0. This confusion may be why most programs don’t allow it. In fact, most programs probably only store Boolean values as numbers because they take up less space when saved that way (1 bit vs 4 or 5 bytes), and back when the ground rules were being established disk space was at a premium. They never expected people to treat it as a number.

The reason why I said they’re only sort of numbers is that, except in OpenOffice Calc, sometimes they’re treated as numbers, and sometimes they’re treated as text. TRUE+FALSE=1, but concatenate(TRUE,FALSE)=”TRUEFALSE” in most programs, and “10” in OpenOffice. Note how I put quotes around “TRUEFALSE” and “10” but not around TRUE and FALSE. That’s because “TRUEFALSE” is now completely text, and “10” is in an ambiguous state. As it is, it’s text. If you ask the program whether it’s a number, it will say no. If you sorted a column with 10 and “10” in it, the number would be sorted before the text was, and so on. However it’s still possible to convert it back to a number, and do math on it. There’s lots of options for doing this, depending on the exact format in question, so next week I’ll start discussing how to convert between text and numbers for all of the common number formats.