Skip to content

Email Newsletter Challenge 1

This week’s challenge is all about testing to see which data records meet specific requirements.

As always, every Spreadsheet Life Newsletter challenge is designed for both Excel and Google Sheets. You can download the spreadsheet files using the links below.

The Challenge

This week, your task is to write a formula to determine which products to Keep or Drop based on the minimum requirements provided.

Think you can solve this one? Download the spreadsheet file using the link above and give it a try.

The Solution

You will need two functions to help you solve this one. The IF function and the AND function.

You will use the IF function to setup your conditional test, and you will use the AND function to enable two logical tests in total.

Start by selecting cell E5, and enter the following formula.

=IF(AND(C5>=$C$15, D5>=$C$16), "Keep", "Drop")

The formula above tests to see if both the Qty Sold and Total Revenue are greater than or equal to the minimum requirements. If both tests return TRUE, the IF function will result in “Keep.”  Otherwise, it will result in “Drop.”

Finally, C15 and C16 are written as absolute references so the formula in cell E5 can be copied down for the remaining data. You can copy the formula down using the fill handle.

And that sums up the solution for your Spreadsheet Life Newsletter challenge for this week!

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

This Post Has 2 Comments

  1. This is so neat! I want to one day be able to solve these on my own. But for now, when you explain the solutions it just makes so much sense! Thanks for doing these.

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