Welcome to issue 17 of the Sheets Insiders membership program.

You can see the full archives here.

AI 🤖 versus brainpower 🧠

For today’s tutorial, I want to share my recent experience comparing an AI-first approach versus using my own brain to summarize some data.

It’s a cautionary tale that yes, although these AI tools are incredibly good at some things, they’re not perfect.

It’s still crucial to apply your own critical thinking.

In this story, the data was in this format:

It’s a record of responses to a Google Form question that had multiple checkboxes. You can see how some rows only have a single choice (e.g. row 2) whereas others have many (e.g. row 8).

What I wanted to do was summarize the data into a table that counted how many times each response happened.

The tool needed to split out the multiple answers to get the correct count.

These new-fangled AI tools have made me lazy, so I figured I’d just copy-paste the data into a chatbot and be done with it.

Let’s see what happened though:

AI tools struggle

I tried ChatGPT and Gemini with this prompt:

can you summarize the reasons why people decided not to join a program, based on these answers? Some lines have multiple answers separated by commas. The output should be a small table with each unique possible answer and a count of how many times it occurs in the list.

I included the data, copied from my Google Sheet.

Here is the result from ChatGPT:

And here is the result from Gemini:

Notice anything?

They gave me wildly different results!

And what’s more, they’re both wrong.

For some reason, probably the fact that some lines had multiple answers, the tools could not count properly.

Eventually, I got ChatGPT over the line by uploading a CSV of the data and asking more basic questions.

I was not able to get correct results with Gemini, even after uploading a CSV.

Anyway, the moral of the story is that sometimes AI struggles with seemingly simple tasks, such as this.

So we’re not quite out of our day jobs yet.

To answer my original question, I had to dust off my formula chops and actually write them myself (oh, the horror!)…

Brains & Formulas to the rescue! 🤓

You can follow these steps anytime you have data that is grouped.

The first step is to split any multiple answers into separate cells:

=SPLIT(A1:A100,",")

(Assuming the data is in column A rows 1 to 100.)

Don’t worry, only the first row will be populated at this stage.

Then, trim any whitespace:

=TRIM(SPLIT(A1:A100,","))

Next, combine all the columns into a single column with TOCOL (or FLATTEN if you prefer):

=TOCOL(TRIM(SPLIT(A1:A100,",")))

And now, for a bit of magic 🪄

Wrap the formula with SORT and it will convert to an array output without requiring the array formula designation.

=SORT(TOCOL(TRIM(SPLIT(A1:A100,","))))

Note: the blank rows will be sorted at the top, so your data may be further down the column.

And finally, we wrap with a QUERY function to group the data and count how many of each response we have:

=QUERY(SORT(TOCOL(TRIM(SPLIT(A1:A100,",")))),
"select Col1, count(Col1) group by Col1 order by count(Col1) desc")

And here’s the result:

This same technique was used to summarize multi-select dropdowns in Sheets Insider 5.

Scores

Here’s our scorecard after today’s experiment:

(Err, thanks to AI for that one…)