FYI: Using AI to Explain Complicated Formulas

Discussion
1

AI’s like ChatGPT v3.5 or v 4.0 are very good at explaining complicated formulas that I often see posted here. I find it very helpful and educational.

For example, this post (Getting tags used per category - #5 by cculber2) included a complicated Query answering a question about summarizing transactions by tag.

This is the formula offered by super hero cculber2:

=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 asked ChatGPT v 3.5, to “Explain this formula from Google Sheets:” This is the AI 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.

  1. 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.

  1. 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.

4 Likes
2

This is so helpful, I’ve played around with it and found it to simplify formulas really well. Another one someone recently told me about is formulabot.com. It is more aimed at going the opposite direction - when you know what you want the formula to do, but not how to put it in the right syntax. Huge time saver for us spreadsheet nerds. :slight_smile:

2 Likes
3

This is a good tip, and pretty much the best use for ChatGPT. Just make sure to verify the output, as it can be wrong or incomplete. I fed it a complicated formula I developed, and it glossed over the most involved part of it, but explained the rest. I extracted the part it kind of touched on and asked about that specifically, and it was able to generate a decent explanation, but was still missing some context.

I’ve also tried using Chat GPT to generate formulas, and that’s a bit more shaky. It will sometimes generate a formula that uses a function that isn’t supported by the platform. A little more prodding, like “Excel doesn’t support ARRAYFORMULA” and it will correct itself and give a better answer.