Using AI for Excel Functions - ChatGPT

[GeoffW]

While it's been a long time since I've done any heavy lifting in Excel, the possibilities of AI to solve Excel coding issues - both functions and VBA - seems quite awesome, though I'd be surprised if the results are robust at this time.

ChatGPT (https://chat.openai.com/chat) has only been around for a couple of months. It's an AI bot tool, with all sorts of awesome capabilities (I've just used it to help write an objection letter to a parking fine). But some of the results are impressive.

At a minimum, it generates answers which are more to the point and with easier-to-find answers than those given by Excel Help - for instance "Excel graph columns 3-5".

A friend used it to solve a VLOOKUP function that had had herself and some experts stumped. Though I'm not as confident about its abilities after plugging in a few questions from this forum. I'd be interested to see the experience of others.

[HansV]

I haven't tried it myself, but I'd hesitate to use it for more than simple questions. An AI bot is not better than the data it is fed with...

[StuartR]

I have seen countless examples of ChatGPT arguing convincingly for something that is factually wrong, even quoting fake sources to justify its assertions.

[DocAElstein]

I did catch something about this a while back, on YouTube I think. It looked very impressive and was a great YouTube success. When I checked a few things then I found something along the lines of what Stuart said. There were some blatant lies in it. (Does not matter for YouTube. YouTube is not, and does not claim to be an open unbiased or honest thing, they do anything to maximise profit and are perfectly happy to support and encourage anything if it gets lots of views. )

But some of these AI Bots generally, are frighteningly good. An example I spent some time fighting with recently is the YouTube comment controlling Bot. There is close to no human involvement any more at YouTube comments but they get read and sorted in order by a controlling Bot/algorithm thing, (or deleted), in any way that encourages views. Happy views are often good for business and is how you can get bad or broken things getting to be a success there, since any short good comment will get put at the top which will tend to be self perpetuating sometimes as people like to click liking happy sounding comments. I think a lot of the most successful channels have spent a lot of time to figure out and work the system, at least to some extent.

Personally that sends a shiver down my spine, - I find things like this very, very scary.
There seems to be zombies taking over the world. They were normal people then they got a smart phone which they talk into to tell them what to do. - Never mind China making another Corona virus, - watch out for the hacker that gets in and takes out the line in Google to tell you to lift the toilet seat – half the population will make a horrible mess and we will all die by some virus caused by poor personal hygiene.


I think one of the things that feeds and makes these things grow is the desire for short answers and questions, and short happy news, apparently solving problems. It leads to short sightedness, which is the like Smart Phone Zombie syndrome. It’s a strange human desire that seems increasingly difficult for people to handle correctly. A while back the owner of a major Excel forum asked me if I would help him in developing a Bot to ask and answer questions, to somehow clone or work in parallel to the “real people” to increase the apparent traffic. I found that a terribly frightening idea, but I can understand the thinking behind it. There seems to be an increasing tendency at the major forums of people turning into short question answering zombies and senior zombies controlling the other zombies to make sure they stay that way. There seems to be a dangerous trend for them to want to kill anyone trying to encourage learning. They seem to be getting like a lot of rats on a tread mill chasing their merit stars or whatever. Maybe they are worried about being out of a “job” if people start learning again to think for themselves.

I think maybe its time to use extreme physical violence on people to force them to learn to think for themselves. JIMO

Alan

[GeoffW]

ChatGPT does come with the disclaimer that it's not necessarily factually correct. If one were to use it for writing, it would be necessary to use output as a starting point, to be factually checked. The trouble is that the prose is so good that wildly incorrect writing can look so good.

I asked it to generate an answer to a thread "Max Year Formula":


Please show me how to get the maximum year for dates in a column in VBA for excel office 365

In VBA for Excel Office 365, you can use the WorksheetFunction.Max method to find the maximum year from a column of dates. This method is a part of the WorksheetFunction object, which provides access to many of the worksheet functions in Excel, such as MAX, MIN, and AVERAGE.

Here's an example of how you can use the WorksheetFunction.Max method to find the maximum year from a column of dates:

Copy code
Sub MaxYear()

Dim maxYear As Integer

' Assume the dates are in column A, starting at row 1
maxYear = WorksheetFunction.Max(Range("A1:A100").Value)

' Use the Year() function to extract the year from the max value
maxYear = Year(maxYear)

' Display the maximum year in a message box
MsgBox "The maximum year is: " & maxYear

End Sub
In this example, the macro assumes that the dates are in column A, starting at row 1 and it finds the maximum value in the range of A1:A100, which you can adjust as per your requirement, Then the macro uses the Year() function to extract the year from the maximum date, and then the macro displays the maximum year in a message box, using the MsgBox function.

It's important to keep in mind that the above example assumes that all the cells in the range are in proper date format. If there are any non-date cells or empty cells, the function will return an error, so it's important to validate the data and make sure that the range contains only valid dates.

[GeoffW]

However, there are things it doesn't do so well

Write a poem about excel

Excel, oh how you organize and sort,
A tool so precise, you make our work a sport.
With rows and columns, you lay it all out,
Making sense of numbers, there's no doubt.

You're a lifesaver for budgets and spreadsheets,
Helping us analyze and forecast trends,
With charts and graphs, you make it all clear,
Helping us make decisions, without any fear.

From tracking sales, to planning expenses,
With you by our side, we have all the defense,
With macros and VBA, you automate with ease,
Making our work a lot more efficient with your keys.

With your simplicity, you conquer complexity,
Excel, you are the master of data and efficiency.
A tool that we all use, and can't live without,
Excel, you're the one we can always count.

[snb]

But what if the dates are in the 'field' 'opname' in the dynamic table 'Table1'
(and only visible rows must be taken into account).

Code: Select all

Sub M_snb()
   MsgBox [year(max(Table1[opname]))]
   MsgBox Evaluate("year(max(Table1[opname]))")
   MsgBox [year(subtotal(4,Table1[opname]))]
End Sub

[HansV]

@snb: Did you perhaps intend to reply to a different topic? For example Max Year Formula