Is there any way to create a macro to keep doing nonlinear regressions?

[elkprj]

Is there a way to create a macro keep doing nonlinear regressions 'till the sum of all regressions give very close points to y results for given x values. It would be better if it create trigonometric regressions like sine or cosine regressions, I do not know whether automatic trigonometric regresion ispossible or not though. I have added a sample file what I would like to achieve, it is not in the US excel format though. Thanks for any help in advance.

[Aussiebear]

Guessing here but maybe this might get you started in the right direction. Paul_Hossler would be much better at this.

Sub IterativeNonlinearRegression()
    ' --- 1. Input Data and Settings ---
    Dim xValues As Range
    Dim yValues As Range
    Dim tolerance As Double
    Dim maxIterations As Integer
    Dim currentError As Double
    Dim iterationCount As Integer
    Dim regressionEquations As Collection 
' To store the equations
    Dim predictedYValues As Variant.  ' To store the combined predicted Y values
    ' Set your input ranges and parameters here
    Set xValues = ThisWorkbook.Sheets("Sheet1").Range("A1:A100") ' Example X range
    Set yValues = ThisWorkbook.Sheets("Sheet1").Range("B1:B100") ' Example Y 
range    tolerance = 0.001 
' Desired level of accuracy    maxIterations = 10 
' Maximum number of regression additions
    Set regressionEquations = New Collection
    ReDim predictedYValues(1 To xValues.Rows.Count, 1 To 1) 
' Initialize predicted Y array

' --- 2. Initial Regression (Optional but Recommended) ---
' You might want to start with a simple model (e.g., a constant or a linear fit) to get the process going. This part would involve setting up Solver with an initial guess for the parameters and running it.
' Store the resulting equation and predicted Y values.

' --- 3. Iterative Loop ---
Do While currentError > tolerance And iterationCount < maxIterations iterationCount = iterationCount + 1
' --- 3a. Add a New Regression Model ---
' This is the most complex part. You'll need a strategy to decide what kind of trigonometric function (sine or cosine) and initial ' parameters to try. This could involve:
' - Trying a predefined set of frequencies or periods.
' - Analyzing the residuals from the previous iteration to guess potential frequencies or amplitudes.
' - Potentially prompting the user for guidance. For example, let's say we try adding a sine wave:

Dim initialAmplitude As Double
        Dim initialFrequency As Double
        Dim initialPhaseShift As Double
        Dim initialVerticalShift As Double

' You'll need logic to determine these initial values initialAmplitude = 1
' Example initialFrequency = 0.1
' Example initialPhaseShift = 0
' Example initialVerticalShift = 0
' Example ' ---

3b. Set up and Run Solver for the New Regression ---
' You'll need a dedicated function in VBA to calculate the predicted Y values for a given trigonometric function and its parameters.
' Then, you'll use the Solver object to minimize the sum of squared errors between the *residuals* of the previous model and the newly added trigonometric function.
' Example function (you'd need to implement this):

Function SinePrediction(x As Double, amplitude As Double, frequency As Double, phase As Double, verticalShift As Double) As Double
    SinePrediction = amplitude * Sin(frequency * x + phase) + verticalShift
End Function

You would set up Solver to adjust the amplitude, frequency, phase, and vertical shift to minimize the sum of squared differences between (yValues - previousPredictedYValues) and the SinePrediction.

' --- 3c. Store the New Regression Equation and Update Predicted Y Values ---
' Once Solver finds a solution for the new regression, store the ' equation (or just the parameters). Update the overall predicted ' Y values by adding the contribution of this new regression.

' --- 3d. Calculate the Current Error ---
' Calculate the sum of squared errors between the current combined ' predicted Y values and the actual yValues.
Loop

' --- 4. Output Results ---
' Display the final regression equations and potentially the predicted Y values.
End Sub
[/CODE]