Excel stores the value for True as 1 and a False as 0. So, if we multiple a cell that contains a Boolean statement by 1, we turn the cell into a 1 if it was originally TRUE, and into a 0 if it was originally FALSE.
By applying this trivial transformation to a range of cells, it often removes the necessity of writing complicated IF statements, or COUNTIF statements when error checking.
For example, you often wish to see whether two columns in Excel are identical (usually when checking another’s work). A standard way is to just use: =(A1 = B1) for adjacent cells A1 and B1, and then copying the formula down. That results in a column of TRUE / FALSE cells, which you can use to ascertain where the columns differ (using various methods).
Although this approach works, it would have been easier (and more useful) to write: =–(A1 = B1) (or: = 1* (A1 = B1)) and then copied this formula down.
This would have resulted in a column of 1s and 0s instead of TRUEs / FALSEs.
Now, if you highlight this new column, the numeric summary of the column will appear at the bottom right of your screen (make sure you have the status bar displaying Count and Sum, of course!). The difference between the count of the column and the sum of the column is the number of 0s, i.e., the number of differences in the two original columns. Specifically, if the count is equal to the sum then the two original columns must have been identical (equivalently, if Min = 1).
You can extend these ideas to using OR functions, AND functions, and others, and thus remove the need for overly complicated IF statements and the like.
For example: = –(AND (A1=B1, B1=C1, C1=D1)) will return a 1 if all four of the specified adjacent cells are equal, otherwise will return a 0.
