Replicating the Logarithmic trendline equation

[RockandGrohl]

Hello guys

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim quantCell As Range
    Dim chartObj As ChartObject
    Dim trendlineEquation As String
    Dim a As Double, b As Double
    Dim quantValue As Double, predictedCost As Double
    Dim posLn As Integer
    Dim parts() As String

    ' Define the Quant1 named range
    Set quantCell = Me.Range("Quant1")

    ' Exit if the change is not in the Quant1 cell
    If Intersect(Target, quantCell) Is Nothing Then Exit Sub

    ' Get the chart object
    Set chartObj = Me.ChartObjects("CurvesChart")
    
    ' Extract the equation from the chart's trendline
    On Error Resume Next
    trendlineEquation = chartObj.Chart.SeriesCollection(1).Trendlines(1).DataLabel.Text
    Range("CostCurve").Value = trendlineEquation
    On Error GoTo 0

    ' Parse the equation (assuming the format "y = a*ln(x) + b" without a multiplication symbol)
    If trendlineEquation <> "" Then
        trendlineEquation = Replace(trendlineEquation, "y = ", "") ' Remove the "y = "

        ' Split the equation into parts
        parts = Split(trendlineEquation, "ln(x)")

        ' Extract coefficient 'a'
        a = Val(parts(0))

        ' Extract 'b' and handle scientific notation
        If InStr(parts(1), "E") > 0 Then
            b = CDbl(parts(1))
        Else
            b = Val(parts(1))
        End If

        ' Debugging output
        Debug.Print "Equation: " & trendlineEquation
        Debug.Print "Coefficient a: " & a
        Debug.Print "Constant b: " & b
    Else
        MsgBox "Trendline equation not found."
        Exit Sub
    End If

    ' Calculate the predicted cost using the extracted equation
    quantValue = Val(quantCell.Value)
    If quantValue > 0 Then ' Ensure that quantValue is positive since log of non-positive numbers is undefined
        predictedCost = a * Log(quantValue) + b
        ' Debugging output
        Debug.Print "Quant1 value: " & quantValue
        Debug.Print "Predicted Cost: " & predictedCost

        ' Set the predicted cost
        Application.EnableEvents = False
        Me.Range("Cost").Value = predictedCost
        Application.EnableEvents = True
    Else
        MsgBox "Quant1 must be greater than 0."
    End If
End Sub

This sub takes the log trendline equation on a scatter chart with multiple data points, and then applies it to x, which is the user-input value in quant1.
I then have another series on the chart set up so that when the user inputs a quant1 figure, it applies a scatter point on the chart in the correct X and Y position on the trendline. It does this by using the trendline equation & the quant1 figure and writing a "cost" figure into the Cost range.

This works on many different configurations on the scatter chart, however when I select a particular set of data, the equation always gives me an incorrect reading.

Here's the equation:

y = 425700ln(x) - 3E+06

For some reason this is putting the user input scatter point far below the trendline. On any other configuration, the user input scatter point lands exactly on the trendline.

y = 54123ln(x) - 192190 gives a correct result,
y = 2827.1ln(x) + 45329 also gives a correct result.

When I do debug print, it gives these values:

Equation: 425700ln(x) - 3E+06
Coefficient a: 425700
Constant b: -3000000
Quant1 value: 1500
Predicted Cost: 113237.918784341

So I know that the code is properly stripping the parameters of the equation out and applying them to my Quant1 figure to give an estimated cost that matches the trendline.


The problem is that a Quant1 of, say, 1500 should give an estimated cost of about £400,000. It's giving an estimated cost of £113,237 - miles away. It seems to be a problem just with this curve.

 

[RockandGrohl]

AHAR

I solved it. ChatGPT can kiss my ***.

That 3E+06 number is scientific notation formatting, and I figured is that rounding the number up?

Turns out it was. Set the chart trendline formatting to number instead of general, and all of a sudden this gives the equation:

y = 425700ln(x) - 2717873

Now do 425,700*ln(1500) - 2717873

And what do you get?

£395,365


BOOYAH