Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets tip 260: Dot plots! π₯ π¦ π©
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:
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
Google Sheets Tips
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #358, your Monday morning espresso, in spreadsheet form! Today's tip comes from an accidental discovery I made whilst researching a trick with the LEFT and RIGHT functions. Let me ask you... What do you think happens with this formula? (Answer before trying it in your Sheet ;) ) = "A" < "B" Answer #VALUE! error TRUE #NUM! error Check out the answer in today's tip and see a real-world application below. β News I.Google...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #357, your Monday morning espresso, in spreadsheet form! Coming to your inbox on a Tuesday because it was a public holiday here in the US yesterday (Happy Labor Day!). I had a friend and his family visit (Mr Data School himself!). We had a great weekend exploring in the woods, watching the kids catch crayfish, and making s'mores over a fire in the evening. I feel recharged (and a little tired) and ready to dive into...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #356, your Monday morning espresso, in spreadsheet form! Iβm delighted to have my friend Justin Keene of EdTech-Training sponsoring this weekβs newsletter. His company teaches educators how to use the Canvas LMS through self-paced learning courses, but hereβs the cool part: he built the entire training platform itself with Google Apps Script! By combining libraries, web apps, and APIs, Justin powers real-time progress...