Brought to you by:

Hi Reader,

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

Halloween is just around the corner and it's huge deal if: 1) you have kids, and 2) you live in America. It's one of the biggest nights of the year, no joke. In my 20s and 30s, before children and living in the UK, it mostly just passed me by ;)

But here we are, busy preparing our costumes. This year my sons have chosen LEGO as the theme. Rest assured I'll share a family photo in due course!

In today's newsletter, we're looking at an elegant formula for finding the factors of a number. There's a lot to like (and learn!) about this formula.

Check it out below.

But first, a message from our friends at Set & Forget:

Brought to you by: Set & Forget Imagine the seamless Google Workspace of your dreams... We'll build it for you! If you're paying for tools that don't meet your needs, or you're lost among all the Google Workspace options, it's time for a change. We specialize in custom Google Workspace development that's designed specifically for your business. Say goodbye to cookie-cutter tools and hello to solutions that streamline your operations and workflow. Why settle for inadequate tools when our developers can muscle your Google Sheets into peak performance? Custom solutions that work as hard as they do, no sweat required! Book a free call to discuss your needs

➜ Sheets Tip #321: An elegant formula built with the 🧅-method

In this tip, we're going to build a beautiful formula to find the factors of a number (the integers that divide a number exactly).

Whether you love mathematics or not, as a Google Sheets aficionado, you'll appreciate this formula.

We're going to build it in steps from the inside out, like layers of an onion 🧅.

Step 1

Let's use the number 321 for this experiment, since we're on tip 321. It's also a very likeable number ;)

So, with 321 in cell A1, enter this SEQUENCE function into cell B1:

=SEQUENCE(A1)

This outputs a column of numbers 1,2,3,...321 in column B.

Step 2

In C1, enter this MOD function, with $ signs around the A1 to lock the reference:

=MOD($A$1,B1)

Copy this formula down column C.

This takes the value in A1, which is 321, and divides it by the value in column B and returns the remainder after the division.

For example, 321 divided by 2 gives a remainder of 1 (2 goes in 160 times with a 1 left over).

Step 3

In column D, let's test to see which rows contain 0, i.e. a remainder of 0 after the division.

Enter this formula in cell D1 and copy down the column:

=C1=0

This gives a column of TRUE/FALSE values, where TRUE indicates rows with 0 remainder.

Since we have TRUE/FALSE values we can use a FILTER function to extract the TRUE rows.

Step 4

Enter this formula in cell E1:

=FILTER(B:B,D:D)

This returns the results from column B wherever there is a TRUE value in column D:

I've highlighted the first two rows with TRUE.

The result is:

1
3
107
321

These are the four factors of 321 (that is, an integer that may be multiplied by some other integer to produce 321).

Step 5

Finally, we can nest the intermediate steps to create a single formula that finds the factors of any number:

=FILTER(SEQUENCE(A1), MOD(A1, SEQUENCE(A1))=0)

The FILTER function is one of my all-time favorite functions.

It works with arrays without the clumsiness of the ARRAYFORMULA designation.

Step 6 - Bonus Challenge :)

Can you modify this final formula to list the factors in a single cell, separated by commas:

1,3,107,321

Good luck!

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

Have a great week!

Cheers,
Ben

P.S. Halloween is almost here and the witches are out!​