Skip to content

Email Newsletter Challenge 14

This week’s challenge will test your ability to write a formula to filter some data. You will be using a fairly new function for this one, so get ready for one awesome challenge!

Download the spreadsheet challenge using the links below.

Challenge

This week, your task is to write a formula that will return any books from the source data written by Brian Tracy that are also less than 300 pages long.

Solution – Google Sheets

The solution is slightly different for Excel and Google Sheets. Here is the solution for Google Sheets.

Select cell B4 and enter the following formula.

=FILTER(F4:H14, G4:G14="Brian Tracy", H4:H14<300)

The FILTER function is designed to reference an original dataset and return only records that meet specific criteria. In this case, the task is to reference all of the listed books and return only those that are written by Brian Tracy (criteria 1) and also less than 300 pages long (criteria 2).

Whenever you use the FILTER function, you always select every column of the original dataset that you potentially want to get back. In this case, you want to get back every column for the data that meets the criteria, so you select the full original dataset (F4:H14).

After that, you enter a specific test for each criteria. Each test is written as another argument for the FILTER function.

So the second argument tests to see which authors in the author column (G4:G14) are equivalent to “Brian Tracy.”

And the third argument tests to see which page lengths in the length column are less than 300.

As a final result, you get back each record that meets both specified criteria.

Solution – Excel

Here is the solution for Excel.

Select cell B4 and enter the following formula.

=FILTER(F4:H14, (G4:G14="Brian Tracy")*(H4:H14<300) )

The FILTER function is designed to reference an original dataset and return only records that meet specific criteria. In this case, the task is to reference all of the listed books and return only those that are written by Brian Tracy (criteria 1) and also less than 300 pages long (criteria 2).

Whenever you use the FILTER function, you always select every column of the original dataset that you potentially want to get back for the first argument. In this case, you want to get back every column for the data that meets the criteria, so you select the full original dataset (F4:H14).

After that, you enter a specific test for each criteria within the second argument. If you include more than one test, you place each test in parentheses and connect them with the multiplication operator (*).

Each test will return an array of TRUE/FALSE values. These arrays are then multiplied together giving you a final array of TRUE/FALSE values. This final array is then used by the FILTER function to determine which records to return.

As a final result, you get back each record that meets both specified criteria.

Thanks for challenging yourself this week, and I hope you had fun and learned something new.

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 2 Comments

  1. super please keep them coming – thanks very much

    btw I used QUERY

    =query(F4:H14,”SELECT * WHERE H<300 AND G='Brian Tracy'")

    what are the pro's and cons of filter and query in thise kind of formula

    1. You got it! More coming for sure! Also, thanks for sharing how you solved it. It’s super cool that you used the QUERY function. The only pros cons that I can think of are the following:

      The QUERY function is certainly more versatile and can handle a much larger variety of situations. So having a good understanding and being able to use the QUERY function is a big pro in itself.

      The only downside I can thing of in regard to the QUERY function is that a lot of people are not familiar with it. So if you are working on a Google Sheets project for others or with others, then there is a small possibility the QUERY function could cause confusion. But then again, you could say that about just any other function as well.

      So I guess the big takeaway here is that there are not too many pros or cons. A lot of it has to do with your own knowledge, skillset, and personal preference.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top