Using ChatGPT with Excel
Can you use ChatGPT to do your Excel work?
Well, ChatGPT can’t actually go out and open up Excel itself and build whatever spreadsheet you want it to build. You still have to do the work.
BUT, it can show you how to set up formulas, especially for simple situations. The problem is most of us already know how to do the simple stuff; it’s the harder complicated situations that we need help with and that we typically end up Googling. At least, I do.
My current assessment of ChatGPT is that if you don’t know Excel, even the simple stuff, then you are probably better off taking a in person class, or an online class, or reading a book. You might be able to bootstrap yourself through Googling, but I really think you need to know the lay of the land – or the lay of the spreadsheet before you know how to ask the question in Google.
So, I’m not interested in asking ChatGPT on how to do simple stuff, even the simple formula set up. I’m more interested in the complicated problems. I was wondering if ChatGPT can come up with a better solution than I have found or even give new suggestions or approaches. I decided to take an old problem which was quite some time ago, so I really had to stretch my memory on the exact situation surrounding the issue.
The Challenge
The challenge came up during the ACA reporting development where sometimes I would see hire dates after the term dates. The question was: were the hire dates keyed in error or did the company rehire the employee? I wanted to be certain because, let’s face it, I found a lot of manual errors or just plain logic errors that I began to distrust everything. I could not automatically think the person was rehired.
But if I could match paychecks to the time periods of employment, then I would feel more certain of the dates.
Now here’s where my memory gets fuzzy. I vaguely remember that the data was not laid out in a simple at a glance fashion and I really wanted something that showed me when the person did not receive paychecks (I call them the missing paychecks) without a lot of gyrations such as copying and pasting and filtering and such, to see such information. I had to scrutinize roughly 3000 rows of data, so I needed something without a lot of work to get at the information.
The solution I came up with at the time was an array formula testing the payroll data and they worked very well in conjunction with the hire/term dates listed in the general employee data. I could start to see when the data logic fell in place: the payroll data either confirmed the general employee information or it did not. If it didn’t, then the data in the employee information was probably an input error.
And this was done without a lot of copying, pasting and filtering. It was just an array formula, albeit a complicated one.
I wanted to see if ChatGPT could come up with something simple or at least simpler.
The Prompt and Conversation
Me: Let’s turn to Excel. Say I have a table of checks for an employee and it’s a list for a year. Column A holds the check date and column B holds the amount. Checks are made every two weeks. Every once in a while, some checks will be missing and not be listed in the table. Or the person quits for a while and then comes back. Is there a way – maybe by formula – to list out the missing checks?
ChatGPT:
Yes, there are a few ways to identify missing checks in a table using Excel formulas. One approach is to use the COUNTIF formula to compare the number of checks in a given time period to the expected number of checks.
For example, if the employee is paid every two weeks, you could use the COUNTIF formula to count the number of checks in a given month, and then compare that to the expected number of checks for that month (i.e. 2 for a bi-weekly pay schedule). If the actual number of checks is less than the expected number, then you know that checks are missing.
Here’s an example of how you might use the COUNTIF formula to identify missing checks:
- In a new column, use the COUNTIF formula to count the number of checks in each month. For example, to count the number of checks in January, you might use the formula =COUNTIF(A:A,”>=1/1/2022″,A:A,”<=1/31/2022″)
- In another column, list the expected number of checks for each month based on a bi-weekly pay schedule.
- Subtract the expected number of checks from the actual number of checks in each month.
- Highlight the cells with a negative value, which means that the actual number of checks is less than the expected number of checks.
Alternatively, you could use the COUNTIFS formula to count the number of checks in a given date range, and then use the IF function to identify missing checks. For example:
=IF(COUNTIFS(A:A,”>=1/1/2022″,A:A,”<=1/31/2022″)<6,”Missing”,”Not Missing”)
This formula will return “Missing” if the number of checks in January is less than 6, and “Not Missing” otherwise.
Another approach is to use the VLOOKUP function to check if the dates in your table are present in a list of expected dates.
Please let me know if you need more information or help with implementing this in your specific case.
I actually like that solution but when I try to remember the situation I was in, I remember feeling like I did not want to do any copying, pasting and filtering. I just wanted “one and done”. My prompt didn’t specify that (my bad), so it gave me an easier solution with a few steps to deploy for the average person. Me, I guess I’m not the average person so I’m more willing to delve into more complicated solutions to get a “one and done” approach.
When creating something that allows flexibility (which is not the problem in this “missing paychecks” problem) or that approaches the “one and done”, something extremely complicated or complex may have to be instituted. I’m okay with complicated.
I do like the solution is offered. I thought that was a good approach.
However, there was an error in the solution. The formula =COUNTIF(A:A,”>=1/1/2022″,A:A,”<=1/31/2022″) as written has two criteria when that particular function can only use one criterium. Those who don’t know Excel functions may not catch this error, well, they will when they try it, but won’t know why. Those who know Excel better will realize that COUNTIFS (with an S) is the function that should be used.
Advice to Myself
I still need to do a LOT of testing of ChatGPT to see what it can and cannot do. This was just a brief foray into ChatGPT, so I really haven’t used it much.
Also, while doing these testing, I might get better at creating the prompts because the answer you get is going to depend on the quality of your prompts. Right now, I’m sure my prompts are rather amateurish. My impression is that the prompts need to be detailed and more precise.
But for the time being, I could try using ChatGPT as an idea generator on how to set up Excel. I think it will give me approaches that I would not have thought of. I just can’t rely on it for more complicated situations – at least not at this point in time.
I’m also using it as an idea generator for topics to write (non-Excel use) or maybe even topics to avoid since it is spitting back the topics that everyone else has written about (it is a probabilistic machine from what I’ve read). Kind of a “been there and done that” situation.
