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]