Extract equation from Trendline box

Hope this helps you fapim:

This parses the retrieved string. It assumes that you have Chart on _separate_ sheet. Open VBA project (Alt+F11) and copy-paste code below. Then run it (F5). In last rows at first worksheet you'll see full equation and separate coefficients of X in correspondent power.

Sub EquProc()
 Dim tmpStr As String, tmpNum As String, tmpX As String
 Dim Power As Boolean, k As Integer, LastRow As Integer
 tmpStr = ThisWorkbook.Charts(1).SeriesCollection(1).Trendlines(1).DataLabel.Text
 LastRow = Worksheets(1).UsedRange.Row + Worksheets(1).UsedRange.Rows.Count
 Worksheets(1).Cells(LastRow + 1, 1) = tmpStr
 k = 1
 Power = False
 tmpNum = "" 
 Do Until Len(tmpStr) <= 0
   Select Case Left(tmpStr, 1)
     Case "0" To "9", ",", "-", "+", "E"
        If Not Power Then
          tmpNum = tmpNum & Left(tmpStr, 1)
        Else
          tmpX = tmpX & Left(tmpStr, 1)
        End If
     Case "x", "X"
        Power = True
        tmpX = "x"
     Case Else
        If Power Then
          Worksheets(1).Cells(LastRow + 2, k) = tmpX
          Worksheets(1).Cells(LastRow + 3, k) = CDbl(tmpNum)
          tmpNum = "" 
          tmpX = "const"
          Power = False
          k = k + 1
        End If
     End Select
     tmpStr = Right(tmpStr, Len(tmpStr) - 1)
   Loop
   Worksheets(1).Cells(LastRow + 2, k) = tmpX
   Worksheets(1).Cells(LastRow + 3, k) = CDbl(tmpNum)
End Sub



Let me know how it goes,
Kom

Dear Bruintje

It was exactly what I want. In fact, I've been used the chart just to get the equation. I never realized that the LINEST function as a matrix, could give me a third degree regression.

By the way, how can I get the R²?

Thanks a lot for your just-in-time answer. If I have more points, certainly I give tehm to you!!!

Best regards,
Fabiano Pimenta
São Paulo - Brazil

NO WAR!!!

thanks for the grade .... but i'm no star in this what is R2?

hmmm it's correlations and stats ...... for more on that you could look here maybe it helps in clarifying what to use in excel or why not

http://hesweb1.med.virginia.edu/biostat/teaching/clinicians/excel.hazards.txt

thanks again bruintje.

Perhaps of use:
http://www.j-walk.com/ss/excel/tips/tip101.htm

Cri, I want to write a third polynomial equation. How can I enter the formula? Like an array formula? My version of Excel is in Portuguese, so the function PROJ.LIN means LINEST, but I don't know what the translate for INDEX. Can you help me?

Fapim, maybe Cri will come back later in the meantime maybe this could be of use

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%239FxvwoqCHA.456%40TK2MSFTNGP09

Bruintje, through this link I found a table very useful:
http://cherbe.free.fr/traduc_fonctions_xl97.html 
By the way, did you saw the cri link? I didn't get to put the formula with INDEX(ÍNDICE).

Perhaps you can use this code

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=55264&page=&view=&sb=&o=&vc=1#Post55264

Remarks:
a) The main reason for me to write this code was that I wanted the approx values and the basepoints in the same value. The disadvantage is that your workbook becomes dependent on your add-in or you need to include the code.

b) The polynomial order is choosen as one less than available points, but of course you can change this or modify this to pass this as parameter.

....basepoints in the same _series_ for easier plotting.

And I think you must write ÍNDICE

very funny. INDICE with i agudo (?)

Cri, in fact I used the function INDEX and LINEST to get the equation parameters (as shown in the link). I got also statistics, like r2, F and Df. According the articles sent by bruintje, Excel is not the best program to get statistics (and I believe that), but as I'm not concerned with precision, I guess this function could be very uesful to me.
Thanks a lot, now I'll test the code.

I'm still using the LINEST function, but I got a problem when the ranges were in vertical position, I mean, I have the variables in columns, not in rows. The output is #VALOR. With data in rows, it's working. How can I solve this?
Txs,
Fabiano Pimenta

My function requireds the x and y values to be in columns and returns the coefficients in a column, using the TRANSPOSE function.