Multiply by True and False in Excel

You can multiply numbers by TRUE and FALSE in Excel. This effectively allows you to eliminate numbers that you don't want from a list.

Sections:

Multiply By TRUE and FALSE

Real World Example

Notes

Multiply By TRUE and FALSE

TRUE = 1

FALSE = 0

When you multiply a value by TRUE, the result is the same as the original value.

When you multiply a value by FALSE, the result is always 0 (zero).

Sample Data:

Let's multiply each value by TRUE or FALSE:

Copy the formula down:

You can see that every line with TRUE is equal to the original number and every line with FALSE is equal to 0.

This isn't a very helpful example though, so now let's examine a more helpful, albeit a lot more complex, example.

Real World Example

There are many basic formulas that can benefit from this feature of Excel but this is most often used in complex array formulas.

In array formulas you can multiply an entire range of numbers by another range of data. When you do this using a range of TRUE and FALSE values, this technique allows you to eliminate the numbers that you don't want by multiplying them by FALSE.

Here is an example that sums data from every x number rows:

=SUM(A1:A6*(MOD(ROW(A1:A6),2)=0))

Array Formula - this is an array formula and so you must enter it into the cell using Ctrl + Shift + Enter.

(This comes from the tutorial Sum Values at an Interval in Excel [LINK COMING])

Result:

(MOD(ROW(A1:A6),2)=0) will evaluate to an array of TRUE and FALSE values.

A1:A6 is the range that is then multiplied by those TRUE and FALSE values.

Every number that is multiplied by FALSE will turn into 0 and only the TRUE values will remain.

As such, this formula sums the values in row 2, 4, and 6, which results in 12.

Now, array formulas can be really tricky, but they can also be really useful once you start to understand them.

Notes

Multiplying values by TRUE and FALSE is not an everyday occurrence for most Excel users but it is a nice little trick to learn because, when you do need to use it, it will feel like magic and allow you to do things you didn't even know was possible.

Make sure to download the sample file for this tutorial to work with the first example in Excel. The second example can be downloaded at the link to its tutorial above.