Using ChatGPT to Explain Complicated Spreadsheet Formulas
AI’s like ChatGPT v3.5 or v 4.0 are very good at explaining complicated formulas I often see posted in the Tiller Community. I find it very helpful and educational.
For example, the post “Getting tags used per category” included a complicated Query answering a question about summarizing transactions by tag. This is the formula offered by Tiller Community superhero cculber:
=QUERY(INDEX(QUERY(SPLIT(FLATTEN(Transactions!B2:B&“|”&Transactions!C2:C&“|”&Transactions!E2:E&“|”&Transactions!F2:F&“|”&SPLIT(Transactions!Q2:Q,“,”,)),“|”),“WHERE Col5 IS NOT NULL”)),“SELECT Col5,SUM(Col4) GROUP BY Col5 PIVOT Col3 LABEL Col5 ‘Tag’”)I wanted to understand how this worked, so I asked ChatGPT v 3.5, to “Explain this formula from Google Sheets.”
This is ChatGPT’s reply:
Certainly! Let’s break down the formula step by step:
1.FLATTEN(Transactions!B2:B&"|"&Transactions!C2:C&"|"&Transactions!E2:E&"|"&Transactions!F2:F&"|"&SPLIT(Transactions!Q2:Q,",",))
This part combines multiple columns from the “Transactions” sheet into a single column. It concatenates the values from columns B, C, E, F, and Q using the “|” symbol as a separator. The SPLIT function is used for splitting the values in column Q by commas (“,”) and then concatenating them.
QUERY(SPLIT(FLATTEN(...)),"WHERE Col5 IS NOT NULL")
This step uses the QUERY function to filter the flattened data from step 1. It selects only the rows where the value in the fifth column (Col5) is not empty or null.
INDEX(QUERY(SPLIT(...)),"SELECT Col5,SUM(Col4) GROUP BY Col5 PIVOT Col3 LABEL Col5 'Tag'")
Here, the INDEX function is applied to the result of the previous QUERY function. It performs another QUERY operation, this time selecting the fifth column (Col5) and the sum of the fourth column (Col4), grouping the data by the fifth column, and pivoting the third column (Col3). It also adds a label to the fifth column, renaming it as ‘Tag’.
In summary, this formula takes data from the “Transactions” sheet, concatenates and splits columns, filters out rows where the fifth column is empty, and finally performs a pivot and aggregation operation. The resulting table shows the sum of values from the fourth column grouped by the values in the fifth column, with the third column providing the column headers, and the fifth column renamed as ‘Tag’.
Clearly worded explanations of complicated formulas like this one is a big help to me and possibly others. I haven’t found any formula, no matter how complicated, that CharGPT v 3.5 couldn’t explain clearly in seconds.
- Read more about ChatGPT + Google Sheets in the Tiller Community.
Note: This post was authored by
Edward:
Yes. I’ve been experimenting with ChatGPT for help with complicated formulas and even Apps Script for Google Sheets. Like you, I’ve been impressed. My challenge is wording the request properly but that’s a good thing because it forces me to think it through.
In one case, it gave me the wrong code for an Apps Script. So, I went back to the same conversation and explained that the code failed and asked it to explain why. Believe it or not, ChatGPT’s answer started with, “I’m so sorry …” Then it proceeded to explain why it failed. Then it gave me the code that worked.
Pretty amazing.
I’ve used it, or tried to use it for Excel but it was really wrong and getting it to the right answer was like pulling teeth. I think it’s good to give you an idea of what to use but you still need to make sure it gives you the right formula. You should always give it sample data and tell it what you want the result to look like.
Here’s an example showing success with ChatGPT helping with a Query in Google Sheets that I was having trouble with.
I’m not an expert on the Query function and I can easily spend a lot of time trying find a syntax error that turns out to be a misplaced comma, parenthesis, etc. I’ve turned to ChatGPT several times recently and it’s helped in every case. This is one example.
I’m building a query in Google sheets to filter data from the transactions tab based on the Category, Account, Group, Type, and Tag. I want the query to return results from the Transactions tab based on a combination of values from one or more of these 5 fields / columns.
The values for each are in consecutive rows in column B. Checkboxes are next to each in column C.
The query will filter the results by matching the values in column B if the checkbox next to each is checked (True). This technique is described in this video:
This is what my Query looks like so far:
Note that in this case, the Where clause will match if the Category in the Transactions sheet “equals” the value in B2 of the Query tab.
By changing the Where clause from “Where =” to “Where contains”, the query will match if the category in the Transactions sheet contains the value in B2.
But I had to manually edit the formula – replacing = with contains and I wondered if I could create another dropdown containing two values, “Contains” or the “=” sign. I modified my Query to reference the value in this dropdown but I couldn’t get it to work.
I put the question to ChatGPT. See below. This is the exact wording of my question and ChatGPT’s answer. It worked. I waited about 3-5 seconds for the answer.
Q: How can I modify this query in Google Sheets such that the first value in the Where clause refers to the contents in cell F3 in the same sheet rather than the literal value “contains.” :=QUERY(Transactions!$B$1:$K,“Select B,C,D,E where D contains '”&B2&“’ and '”&C2&“’ = ‘TRUE’”,1)
A: You can use the INDIRECT function to reference the contents of cell F3 in the Where clause of your QUERY function. The modified query would look like this:
=QUERY(Transactions!$B$1:$K,“Select B,C,D,E where D “&INDIRECT(“F3”)&” '”&B2&“’ and '”&C2&“’ = ‘TRUE’”,1)
I copied and pasted this into my sheet and it worked perfectly.
Note: At this point, the formula refers only to one of the 5 columns … Category. I believe I can add the other four fields by adding an OR clause (4) and shifting the row references in columns B and C down by 1.
Wow! That is very cool! Can’t wait to see how Microsoft deploys this for Excel. MS is exploring using OpenAI-based tools for Word, Powerpoint, and Outlook. I also can’t wait to see how Google responds.
I didn’t but I will now.