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
  • Last Modified:

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
Asked:
fapim
  • 6
  • 5
  • 4
  • +1
1 Solution
 
bruintjeCommented:
Hello Fapim,
not sure but could this help?
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

HAGD:O)Bruintje
0
 
komunizmusCommented:
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
 
fapimAuthor 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bruintjeCommented:
thanks for the grade .... but i'm no star in this what is R2?
0
 
bruintjeCommented:
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
 
fapimAuthor Commented:
thanks again bruintje.
0
 
criCommented:
0
 
fapimAuthor 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
 
bruintjeCommented:
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
0
 
fapimAuthor Commented:
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).
0
 
criCommented:
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.
0
 
criCommented:
....basepoints in the same _series_ for easier plotting.

And I think you must write ÍNDICE
0
 
criCommented:
very funny. INDICE with i agudo (?)
0
 
fapimAuthor 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
 
fapimAuthor 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
 
criCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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