Is chatGPT the way to go with Excel

[Aussiebear]

On the surface chatGPT seems to have unlimited possibilities to enhance a multitude of solutions. Can anyone offer examples where the AI has been of assistance within Excel and or VBA? Where possible please provide a sample to reference your argument?

[georgiboy]

I like to use it to help me with the new 365 functions for example:

Q: Give me an example of using the MAP function in excel 365
A: The MAP function can be used to apply a given function to each element of an array or range, and return an array of the results. Here's an example of how you can use the MAP function in Excel 365:

Suppose you have a list of numbers in column A, and you want to calculate their squares using the MAP function. You can follow these steps:


Select an empty cell where you want the results to appear, such as cell B1.
Enter the formula =MAP(A1:A10, x -> x^2) in the cell B1. This formula applies the square function (x -> x^2) to each element of the range A1:A10 using the MAP function, and returns an array of the squared values.
Press Enter to calculate the results.
The resulting array will contain the squares of the numbers in column A. You can then copy the formula down the column to apply it to other ranges or lists of numbers.

--------------

It is also very good at annotating code:
I asked it to annotate the below:

Sub test()
Dim rng As Range, unq As Variant, x As Long, wb As Workbook, ws As Worksheet
Dim hVar As Variant, rCount As Long

Set rng = Sheet1.ListObjects("Table1").DataBodyRange


hVar = Sheet1.Range("A1:F1").Value

unq = Application.Unique(Application.Index(rng, , 2))

For x = 1 To UBound(unq)
Set ws = Sheets.Add(, Sheets(Sheets.Count))
ws.Name = unq(x, 1)
ws.Range("A1:F1") = hVar
rCount = Application.CountIf(Sheet1.Range("B:B"), unq(x, 1))
ws.Range("A2:F" & rCount + 1) = Evaluate("FILTER(Sheet1!A:F,Sheet1!B:B=""" & unq(x, 1) & ""","""")")
ws.Range("A:F").EntireColumn.AutoFit
Next x

Sheets(Application.Transpose(unq)).Move
End Sub

It gave me:

Sub test()
Dim rng As Range, unq As Variant, x As Long, wb As Workbook, ws As Worksheet
Dim hVar As Variant, rCount As Long

'Declare variables
Set rng = Sheet1.ListObjects("Table1").DataBodyRange 'Define the range of data to be analyzed

hVar = Sheet1.Range("A1:F1").Value 'Store the header row of the data in a variable

unq = Application.Unique(Application.Index(rng, , 2)) 'Identify unique values in the second column of the data and store them in a variable

'Loop through the unique values and create a new worksheet for each value
For x = 1 To UBound(unq)
Set ws = Sheets.Add(, Sheets(Sheets.Count)) 'Create a new worksheet
ws.Name = unq(x, 1) 'Name the worksheet with the unique value
ws.Range("A1:F1") = hVar 'Copy the header row to the new worksheet
rCount = Application.CountIf(Sheet1.Range("B:B"), unq(x, 1)) 'Count the number of rows with the unique value
ws.Range("A2:F" & rCount + 1) = Evaluate("FILTER(Sheet1!A:F,Sheet1!B:B=""" & unq(x, 1) & ""","""")") 'Copy the rows with the unique value to the new worksheet
ws.Range("A:F").EntireColumn.AutoFit 'Auto-fit the columns in the new worksheet
Next x

Sheets(Application.Transpose(unq)).Move 'Move the new worksheets to the front of the workbook in the order of the unique values
End Sub


-----------

It works very well as an assistant but it still makes a lot of mistakes and generally can be confused about the new dynamic array formulae in 365, it is better than BARD at this point as BARD does not seem to support code or formulae yet.

[Aussiebear]

As I understand it, you still need to be fairly precise in the questions you put to chatGPT to get the right type of answers

[georgiboy]

Yes very, as with a web search you also need to keep submit the question multiple times to get to the right answer, the problem with that is unless you already have some knowledge of the solution you may miss the right answer.

It is also a good idea to start your question with your current version i.e.
Q: "How do i work out an average of each row in range A2:D16 using the BYROW function in excel 365?"
A: The BYROW function is not a built-in function in Excel 365, so it is not possible to provide an example using this function. However, you can use the AVERAGE function in Excel 365 to calculate the average of each row in a range of cells.

If i then say:
"I have the BYROW function"

It gives me the below (Which does not work):

=BYROW(A2:D16,AVERAGE,1)

I then made the question:
"Get average of A2:D16 using BYROW and LAMBDA with excel 365"

After clicking regenerate response 4 times it gave me the below answer (this works):

Yes, you can use the BYROW and LAMBDA functions in Excel 365 to calculate the average of each row in the range A2:D16.
First, you need to define a Lambda function that calculates the average of a given range. Here's the formula you can use to define the Lambda function:

=LAMBDA(rng,AVERAGE(rng))

This formula defines a Lambda function that takes a range of cells as input (specified by the rng parameter) and returns the average of the range using the AVERAGE function.


Next, you can use the BYROW function to apply this Lambda function to each row in the range A2:D16. Here's the formula you can use in cell E2:

=BYROW(A2:D16,LAMBDA(rng,AVERAGE(rng)))
This formula uses the BYROW function to apply the Lambda function to each row in the range A2:D16, which calculates the average of each row using the AVERAGE function.


After entering this formula into cell E2, you should get the average of the first row in the range. You can then copy this formula to the cells below (E3:E16) to calculate the averages for each row.

------

For that reason i would say that it is good for people like us who have a better understanding of Excel but not so great if the user thinks it can automate all of their projects. I see it less of a an AI and more of a AA Artificial Assistant.

I have found it to be good with stats as well, you can paste some data into it with revenues, profits, tax's etc.. as long as their is a week number/ date and headers etc.. you can ask it to tell you about the stats and it will tell you the highest/ lowest weeks profit and the % of gain between the weeks and anything else it finds. Can help out padding out a board report for example.

[georgiboy]

I take back what i said about BARD as it would seem it has been updated to work with VBA and Formulae.

[Aflatoon]

I have seen numerous threads where people have used chatGPT to generate VBA code and are then stumped when it doesn't work. Often it is because the AI appears to have just completely made up methods or properties. (There is at least one 'helper' on the Answers board who appears to me to use it regularly because the code they post looks plausible at first glance but is actually complete nonsense when you look closely). That's why it's currently banned on a few forums.

[Aussiebear]

Funny that you mentioned that Aflatoon, because I have been experimenting with chatGPT this evening and the results are woeful. using the data that came up earlier in the Einsener37 thread regarding Interpolation in Excel. I asked chatGPT to interpolate the data in a specified range and up came 3 interpretations, none of which seem to work. Either nothing happened or it indicated an Error 9 subscript out of range issue. I have included the workbook as the example. Here is the 3 variations that chatGPT offered.



InterpolationDataInRange #1
Sub InterpolateDataInRange1()
Dim dataRange As Range
Dim xValues() As Variant
Dim yValues() As Variant
Dim rowCount As Integer
Dim colCount As Integer
Dim x As Double
Dim y As Double
Dim i As Integer
Dim j As Integer
Set dataRange = Range("A32:I42")
rowCount = dataRange.Rows.Count
colCount = dataRange.Columns.Count
ReDim xValues(1 To rowCount * colCount)
ReDim yValues(1 To rowCount * colCount)
i = 1
For j = 1 To colCount
For Each cell In dataRange.Columns(j).Cells
xValues(i) = cell.Row
yValues(i) = cell.Value
i = i + 1
Next cell
Next j
For i = 1 To rowCount * colCount
If yValues(i) = "" Then
If i = 1 Or yValues(i - 1) = "" Then
' Extrapolate from the first two data points
x = xValues(i)
y = yValues(i + 1) - ((xValues(i + 1) - x) / (xValues(i + 1) - xValues(i))) * (yValues(i + 1) - yValues(i))
ElseIf i = rowCount * colCount Or yValues(i + 1) = "" Then
' Extrapolate from the last two data points
x = xValues(i)
y = yValues(i - 1) + ((x - xValues(i - 1)) / (xValues(i) - xValues(i - 1))) * (yValues(i) - yValues(i - 1))
Else
' Interpolate between two adjacent data points
x = xValues(i)
y = yValues(i - 1) + ((x - xValues(i - 1)) / (xValues(i) - xValues(i - 1))) * (yValues(i) - yValues(i - 1))
End If
dataRange.Cells(i Mod rowCount + 1, i \ rowCount + 1).Value = y
End If
Next i
End Sub

InterpolationDataInRange #2


Sub InterpolateDataInRange2()
Dim dataRange As Range
Set dataRange = Range("A47:I57")
'Extract x and y data from the range
Dim xData() As Variant
Dim yData() As Variant
xData = Application.WorksheetFunction.Transpose(dataRange.Rows(1).Value)
yData = Application.WorksheetFunction.Transpose(dataRange.Rows(2).Value)
'Calculate the interval between adjacent x values
Dim interval As Double
interval = xData(2) - xData(1)
'Interpolate missing data points
Dim i As Long
Dim j As Long
Dim slope As Double
Dim yIntercept As Double
For i = 1 To UBound(xData)
If IsEmpty(dataRange.Cells(2, i)) Then
j = i - 1
'Find the previous x value that is not empty
Do While IsEmpty(dataRange.Cells(2, j))
j = j - 1
Loop
'Calculate the slope and y-intercept of the linear equation that passes through the adjacent data points
If j = 1 Then
slope = (yData(j + 1) - yData(j)) / interval
yIntercept = yData(j) - slope * xData(j)
ElseIf j = UBound(xData) Then
slope = (yData(j) - yData(j - 1)) / interval
yIntercept = yData(j) - slope * xData(j)
Else
slope = (yData(j + 1) - yData(j - 1)) / (2 * interval)
yIntercept = yData(j) - slope * xData(j)
End If
'Calculate the interpolated value and write it to the missing cell
dataRange.Cells(2, i).Value = slope * xData(i) + yIntercept
End If
Next i
End Sub


InterpolationDataInRange #3

Sub InterpolateDataInRange3()
Dim rng As Range
Dim cell As Range
Dim x() As Variant
Dim y() As Variant
Dim n As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim interval As Double
Dim slope As Double
Dim yIntercept As Double
Set rng = Range("A62:I72")
n = rng.Cells.Count
ReDim x(1 To n)
ReDim y(1 To n)
i = 1
For Each cell In rng
x(i) = cell.Column
y(i) = cell.Value
i = i + 1
Next cell
interval = x(2) - x(1)
j = 1
For k = 1 To n
If IsEmpty(rng.Cells(k)) Then
Do While IsEmpty(rng.Cells(j))
j = j + 1
Loop
If j = 1 Then
slope = (y(j + 1) - y(j)) / interval
yIntercept = y(j) - slope * x(j)
ElseIf j = n Then
slope = (y(j) - y(j - 1)) / interval
yIntercept = y(j) - slope * x(j)
Else
slope = (y(j + 1) - y(j - 1)) / (2 * interval)
yIntercept = y(j) - slope * x(j)
End If
rng.Cells(k).Value = slope * x(k) + yIntercept
End If

j = j + 1
Next k
End Sub


Can anyone suggest how any of the above sets of code could be corrected or are they complete rubbish?