Skip to content

Email Newsletter Challenge 21

The weekly spreadsheet challenges are back! This week’s challenge is simple but a little tricky.

Think you’re up to the task? Download the spreadsheet challenge using the links below.


This week, your task is to first filter the data by the color “Green.” Then you must calculate the average age based only on the filtered results.


The solution is the same for both Excel and Google Sheets.

The tricky part about this solution is that you want to write the formula before you filter the data. Select cell H3, and enter the following:

=SUBTOTAL(1, E4:E23)

The SUBTOTAL function can be used to calculate all kinds of metrics. Sum, average, count, and many more. Setting the first argument to a 1 causes the function to calculate the average.

Now the main reason we use the SUBTOTAL function here is because the SUBTOTAL function will only consider the filtered results in it’s calculation. If you were to use the AVERAGE function instead, it would include the results that have been filtered out. And this is not what we want.

Therefore, using the SUBTOTAL function is the best choice. You can also learn more about the SUBTOTAL function here.

If you still have questions, please leave me a comment below so I can clarify things for you. I’m here to help! 😊

I hope you had fun, felt challenged and learned something new. And if you’re not signed up for my weekly email newsletter, you can sign up right here!

P.S. – Thank you to those who have encouraged me to keep the weekly spreadsheet challenges coming. It’s because of you that I’ve decided to keep them around. I really appreciate each and every one of you!

This Post Has 0 Comments

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