Regression formula

**Romoluzzi** · 12-22-2010, 06:13 AM

I often use regression in data analysis and produce relevant graph.
Here's the code:

Sub Previsioni()
'
If Range("D30") = 42 Then
MsgBox "Attenzione!!! Zona dati vuota"
End
End If

Dim Codice, Tipo, equaz
equaz = ""
Codice = Range("f5").Value
Codice = Left(Codice, 1)

If Codice = 1 Then
Tipo = xlLinear
End If

If Codice = 2 Then
Tipo = xlExponential
End If

If Codice = 3 Then
Tipo = xlLogarithmic
End If

If Codice = 4 Then
Tipo = xlPolynomial
End If

ActiveSheet.ChartObjects("Chart 31").Activate
ActiveChart.ChartArea.Select

ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=Tipo, Forward:=0, _
Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select

ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
'equaz = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel
Selection.Left = 440
Selection.Top = 273
ActiveWindow.Visible = False

'Range("Equazione").Value = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel
'Selection.Paste
End Sub

As you can see I've tried to "catch" automatically in some way the formula given by the trendlines in the graph area in order to use it for other calculations, but unsuccessful.
Have you any hint on how this can be achieved?
Thanks in advance for your usual precious help.

**Romoluzzi** · 01-01-2011, 10:43 AM

No suggestions? not even for the new year?

**shg** · 01-01-2011, 12:26 PM

You can use LINEST to get the parameters of the regression.

If you post a workbook with an example, I or someone else will show you how.

**MarvinP** · 01-01-2011, 01:18 PM

Does the link below help in resolving your desire?

http://zimmer.csufresno.edu/~davidz/...INESTfull.html

**Romoluzzi** · 01-02-2011, 07:38 AM

Here's is another evidence that (almost) always the simplest solution is the hardest to find out..... I hadn't thought about using Excel's LINEST function, my fault.
But at this point I have another question: is there any way to use the 10 parameters generated by LINEST in VBA code? In other words, is it possible in a macro / UDF to set a variable to get the value of the slope, the intercept, etc and to use it in further calculations, WITHOUT actually inputting the array formula in a ten-cell area of the worksheet and/or WITHOUT set the calculation in the VBA procedure?
For example, if I use the macro recorder and input the LINEST formula in the worksheet, I get:

Sub Macro2()
    Range("K41:L45").Select
    Selection.FormulaArray = _
        "=LINEST(R[-33]C[-8]:R[-26]C[-8],R[-33]C[-10]:R[-26]C[-10],TRUE,TRUE)"
End Sub

I see that there is a VBA instruction to calculate the parameters; is it possible to put some of the 10 values DIRECTLY in variables WITHOUT using the corresponding cells values in the worksheet?

I hope my question is clear and I thank you all for your support.

**DonkeyOte** · 01-02-2011, 07:51 AM

Do you mean along the lines of:

Dim vResults As Variant
With Sheets("Sheet1").Range("A8:A15")
    vResults = .Parent.Evaluate("=LINEST(" & .Offset(, 2).Address & "," & .Address & ",TRUE,TRUE)")
End With

vResults would be a 2d Array of the results.

**shg** · 01-02-2011, 08:06 AM

Or

    Dim v As Variant

    v = WorksheetFunction.LinEst(Range("B1:B10").Value, Range("A1:A10").Value, , True)
    MsgBox "Slope:=" & v(1, 1) & vbLf & _
           "Intercept:=" & v(1, 2)

**DonkeyOte** · 01-02-2011, 08:17 AM

oops... I do like to over engineer every now and then !

**Romoluzzi** · 01-02-2011, 08:43 AM

I wish to thank both DonkeyOte and shg for their valuable help.

**shg** · 01-02-2011, 11:26 PM

I do like to over engineer every now and then !

Well, maybe. The versatility of the Evaluate function is something that I continue to enjoy seeing good examples of, as here, and you provide great examples. Among other things (if not in this specific case), Evaluate permits the use of the literal arrays that are so convenient in formulas.