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 A216 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 A216 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 A216.
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 A216. 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 A216, 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?