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.
Guessing here but maybe this might get you started in the right direction. Paul_Hossler would be much better at this.
' --- 2. Initial Regression (Optional but Recommended) ---Code: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
' 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:
' You'll need logic to determine these initial values initialAmplitude = 1Code:Dim initialAmplitude As Double
Dim initialFrequency As Double
Dim initialPhaseShift As Double
Dim initialVerticalShift As Double
' 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):
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.Code: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
' --- 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]