Abstract
Transcript
In this video, we'll look at some ways you can convert TRUE and FALSE values in Excel to 1s and 0s.
When working with more advanced formulas, especially array formulas, you need to know how to convert TRUE and FALSE values in Excel to their numeric equivalents, 1 and 0.
This is best explained with an example.
In this worksheet, I have a list of fruit names.
Let's count all the names with more than 5 characters.
I'll first get the length of each name with the LEN function.
If I give LEN the entire range, results spill onto the worksheet into a dynamic array.
Now, to get a TRUE or FALSE result, I'll convert the formula to a logical expression that checks if the length is greater than 5.
=LEN(B5:B26)>5
The result is a set of Boolean values. For each name, we get a result of TRUE or FALSE.
Now, if I feed this range into the SUM function, we get zero as the result.
Why is that?
This is because TRUE and FALSE are logicals, not numbers.
And the SUM function only works with numbers.
We need a way to convert these TRUE and FALSE values to their numeric equivalents, 1 and zero.
A good way to do this is with any math operation.
I'll first wrap the formula in parentheses in order to control the order of operations. We want the numeric conversion to happen last.
One option is to simply add zero.
=(LEN(B5:B24)>5)+0
Or, I can multiply by 1
I can also use the N function which converts values to numbers.
=N(LEN(B5:B24)>5)
My personal favorite is to use a double negative, because it's easy to type, and visually, it signals a numeric conversion.
=--(LEN(B5:B24)>5)
But each method works fine.
In each case, we get either 1 or zero.
Now that we've converted the Booleans to numbers, the SUM formula works properly, and we can see that 11 fruits have names with more than 5 characters.
Finally, I've been using a helper column to more easily visualize these operations, but typically, we'd put everything into a single formula.
I can just copy the formula from C5, and then paste into the SUM function.
=SUM(--(LEN(B5:B24)>5))
If I check the operation inside sum, with the F9 key, I can see that we get the same array, and the SUM function returns the same result.
I can now remove the helper column, and everything will still work.
