This week ChatGPT solved a small but real problem at work. It wasn’t a very exciting problem: I needed to write a formula in Excel, and I couldn’t work out how to do it.
Up until now, I’ve only played around with ChatGPT
It wrote some funny poems and that was pretty impressive. A few things came up at work where I thought it might help, but the text it generated was bland and not much use.
This time, it solved a real problem
Large Language Models are currently in an extreme hype stage. It’s easy (and maybe a healthy first instinct) to be skeptical about the next big thing in tech. That said, it has an impact when you find yourself using the next big thing to help with a mundane work problem.
I tried Googling for the formula at first, but I had a few problems:
- I didn’t know how to condense my query into a set of keywords that would get me the answer I needed.
- When I tried some possible keywords, the results took me to some cluttered websites with way more information than I needed. Sorting through the junk didn’t get me to the right answer
- Excel has a ton of functions, and knowing which one to pick isn’t always a straight choice. Sometimes you can do the same thing in two different ways. But what you want is the best way.
ChatGPT’s answer solved all three of these problems. I could ask my question in natural language. The response that came back was clear and to the point. There was one function recommended.
It also solved the main problem at hand: the formula worked.
It’s like you’re asking a colleague for help, but your colleague is a robot
One of my favourite ways to learn computer stuff is:
- Encounter a genuine problem
- Have a go at solving it
- Get stuck
- Ask for help
- Gain knowledge that helps me solve the problem
- Solve the problem
Next time the problem occurs, I can use the knowledge gained to solve it myself.
Normally, at step 4, I ask a human for help, or I do a search online. In this case, ChatGPT seemed to occupy a middle ground between the two. I was able to ask for help using the same words and phrasing I might use if I was writing an email to a colleague.
Maybe I’m caught up in the hype a bit, but there’s something kind of exciting about that.
Here’s the chat log, recorded for posterity
—STARTS—
Me: I’ve created an Excel workbook that has 2 sheets, X and Y. In Sheet X, there are two columns, A and B. I need an Excel formula for Sheet Y that counts the number of times — in Sheet X — that a row has “yes” in column A and “no” in column B.
ChatGPT: You can use the COUNTIFS function in Sheet Y to count the number of times “yes” appears in column A and “no” appears in column B of Sheet X. Here’s the formula you can use:
=COUNTIFS(X!A:A,"yes",X!B:B,"no")
This formula will count the number of times “yes” appears in column A of Sheet X and “no” appears in column B of Sheet X. Make sure to replace “X” with the actual name of your Sheet X.
You can enter this formula in any cell in Sheet Y where you want to display the count result. The formula will update automatically if any changes are made to Sheet X.
Me: That’s great, thanks. I don’t need an exact match for the words “yes” or “no” though. I just need to know if the cell contains that string somewhere in it.
ChatGPT: Sure, you can modify the formula to use the wildcard character “*” with the COUNTIFS function. Here’s the updated formula:
=COUNTIFS(X!A:A,"*yes*",X!B:B,"*no*")
This formula will count the number of times “yes” appears anywhere in the cells of column A in Sheet X and “no” appears anywhere in the cells of column B in Sheet X.
The “*” wildcard character is used to match any string of characters, so the formula will count cells where “yes” or “no” appear anywhere in the cell, not just as exact matches.
Again, make sure to replace “X” with the actual name of your Sheet X.
—ENDS—
