Multiply by True and False in Excel

Add to Favorites
Author:

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:

acc505622ba4ecc03a15d1ce4ff400c8.png

Let's multiply each value by TRUE or FALSE:

80d4b5465c4c8276a8a323666fdf6e6b.png

Copy the formula down:

af7b7564bf9ff843a6c55ab6c99475eb.png

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])

d6239eaf44c7ebe32d17fc041f36b2f5.png

Result:

dad6ecdf470d7b62297cc68fa747bcdd.png

(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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Compare Values in Excel - Beginner to Advanced
Tutorial: How to compare text, numbers, and dates in Excel - including case sensitive text comparis...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
Count Cells Containing TRUE or FALSE in Excel
Tutorial: Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count...
NOT Function - Change False to True and True to False in Excel
Tutorial: Change True to False and False to True with this simple function in Excel. To do this, we...
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
Macro: This free Excel UDF allows you to get the numbers out of a cell that contain both text...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course