Hi Reader,

Welcome to the Google Sheets Tips newsletter #292, your Monday morning espresso, in spreadsheet form!

Quick question before this week's email:

If you use Apps Script, what do you use it for? Follow up, what pain points do you have?

(Feel free to just hit reply to this email.)

A big thank you to this week's sponsors who help keep this newsletter free to read:

➜ New videos on YouTube

🎥 Count Specific Days Between Two Dates in Google Sheets

(inspired by last week's newsletter on counting Mondays)

_______

➜ Google Sheets Tip #292: Finding minimum and maximum names from values

Suppose we have this dataset:

We want to identify the customers with the minimum and maximum revenues. There are many ways to do this, including pivot tables, sorting the data, formulas, etc.

Today, we'll use the powerful FILTER function to find the min/max customers.

First, find the minimum revenue value with this MIN formula:

With the data shown above, this gives us the result of $1,260.

Now, we want to find the customers associated with this value. To do that, let's use the FILTER function.

The filter formula returns values that satisfy specific conditions. It takes two arguments, firstly a range to filter and secondly, the conditions to apply.

We want to find the customer names for the minimum values, i.e. we want to filter the names. So the first argument in the FILTER is the customer names range in A2:A11.

The second argument of our FILTER — the conditions to test — is a test to find when the revenue values (B2:B11) are equal to the minimum value.

We already found the minimum with the MIN formula above.

Hence, our complete formula is:

In our Sheet:

I like the FILTER function because it returns more than one result if there are multiple customers with the minimum value.

Find the Top Customer(s) with MAX

To calculate the top customer, swap the MIN function for a MAX function in the formula.

Neater with Named Ranges

Do you use named ranges?

They're a method for attaching names to cell ranges in your Sheets. Then, instead of referring to a range as B2:B11 in your formulas, you can use the name, e.g. revenue.

To create a named range, highlight our data (e.g. B2:B11) and go to the menu: Data > Named ranges

Enter a name in the name box and click Done.

Now we can use this named range inside our formulas and it points to the correct range. For example, the MIN formula above becomes:

Which, in our Sheet, looks like this:

If we name the range A2:A11 as customers, then our complete FILTER formula becomes even cleaner:

In our Sheet:

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Anyone else do this?​