Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 527

# Extract equation from Trendline box

I need to extract a text from a trendline equation in a chart. How can I put the equation in a cell. And if it's possible, how can I put all the variables in each cell.
i.g. the equation is y= 0,052x2 + 17,2x + 180 (inside the box of a trendline)and I want to get this: A1= 0,052; A2= 17,2 and A3= 180. Anyone can help me?
0
fapim
• 6
• 5
• 4
• +1
1 Solution

Commented:
Hello Fapim,
not sure but could this help?
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

HAGD:O)Bruintje
0

Commented:
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
0

Author Commented:
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!!!

0

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

Commented:
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
0

Author Commented:
thanks again bruintje.
0

Commented:
0

Author Commented:
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?
0

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

0

Author Commented:
Bruintje, through this link I found a table very useful:
By the way, did you saw the cri link? I didn't get to put the formula with INDEX(ÍNDICE).
0

Commented:
Perhaps you can use this code

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.
0

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

And I think you must write ÍNDICE
0

Commented:
very funny. INDICE with i agudo (?)
0

Author Commented:
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.
0

Author Commented:
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
0

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

## Featured Post

• 6
• 5
• 4
• +1
Tackle projects and never again get stuck behind a technical roadblock.