Hi Reader,

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

➜ News

I.
We're getting more information on how Google is bringing generative AI to Workspace, including Google Sheets.

The details concerning Sheets are pretty scant at this stage, but it's going to be interesting to see it emerge:

Learn more about Duet AI and Google Sheets

_______

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

FORMULA STUDIO

Introducing Formula Studio: AI-powered formula magic for Google Sheets! ✨

Simplify editing, create formulas effortlessly, and translate old cell formulas with ease. Experience the future of spreadsheet productivity now!

Click to Learn More!

SET & FORGET

Automate your work. Scale your operations.

Set & Forget can help you streamline your processes and unlock the full potential of automation within Google Workspace.

Book A Consult Today

If you're interested in reaching this audience through a sponsorship, please see the details in the footer of this email.

_______

➜ Google Sheets Tip #260: Dot plot formulas

In this tip, we look at dot plots!

I covered them before in tip 188, but we do something different with this example.

Let's begin with a quick rundown of the functions that we'll use to build the dot plot.

Required Functions

The CHAR function converts a number into a special character, in this case the red, blue, or green square.
The REPT function repeats a text string a set number of times.
The IFS function is like an IF function with more than one conditional test.
MIN and MAX functions output the minimum or maximum values respectively.

Step 1: Single color dot plot

Let's begin by constructing this simple dot plot:

The formula uses CHAR to create the green square and REPT to repeat it the correct number of times.

=REPT(CHAR(129001),A2)

This same technique can be used to create star charts.

Step 2: Partial multicolor dot plot

We change the number inside the CHAR function to generate blue and red squares, in addition to the green square.

Then we use an IFS function to show green for higher numbers, blue for middle numbers, and red for lower numbers.

=IFS( A2>6 , CHAR(129001) , A2>3 , CHAR(128998) , TRUE , CHAR(128997))

Once we have that, we can wrap it with the REPT function to repeat the colored squares the correct number of times:

=REPT( IFS( A2>6 , CHAR(129001) , A2>3 , CHAR(128998) , TRUE , CHAR(128997)), A2)

What if we want to combine the colored squares to a single chart though?

Let's see how to do that!

Step 3: Full multicolor dot plot

In this example, we include the color changes inside each chart:

We make use of MIN and MAX to determine how to fill the "buckets":

1 to 3 are red
4 to 6 are blue
7 to 10 are green

For example, 8 is 3 red + 3 blue + 2 green.

However, 5 is just 3 red + 2 blue.

To show the red portion, use this formula which caps the value at 3 (the maximum number of red squares):

=MIN(A2,3)

We wrap this with a REPT function to show the correct number of red squares:

=REPT(CHAR(128997),MIN(A2,3))

The blue squares are generated by this formula, which caps the max value at 6 and the minimum value at 3, before subtracting 3 (representing the existing red squares):

=REPT(CHAR(128998),MAX(MIN(A2,6),3)-3)

And the green squares are generated by this formula, which sets the minimum to 6 and then subtracts 6 (representing the red and blue squares):

=REPT(CHAR(129001),MAX(A2,6)-6)

This image shows the breakdown of the values into the "buckets" above the dotted black line, and shows the colored squares below the dotted black line:

Finally, we combine the three REPT formulas with "&":

=REPT(CHAR(128997),MIN(A2,3)) & 
REPT(CHAR(128998),MAX(MIN(A2,6),3)-3) & 
REPT(CHAR(129001),MAX(A2,6)-6)

It looks complex but all it's doing is saying take the value and split it into the three buckets: 1 to 3, 4 to 6, and 7 to 10. Then color the squares accordingly and re-combine.

I'll leave it as an exercise for you to create an array version of this formula that only needs to be entered once at the top of the column.

Even better if you can then turn it into a named function that could be used to easily create dot plots... 🤔

_______

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

Have a great week!

Cheers,
Ben

P.S. Sitting, standing, and lying at the same time