?
Solved

Extract equation from Trendline box

Posted on 2003-03-24
16
Medium Priority
?
518 Views
Last Modified: 2008-02-01
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
Comment
Question by:fapim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 300 total points
ID: 8197457
Hello Fapim,
not sure but could this help?
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

HAGD:O)Bruintje
0
 

Expert Comment

by:komunizmus
ID: 8197543
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 Comment

by:fapim
ID: 8197738
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 44

Expert Comment

by:bruintje
ID: 8197799
thanks for the grade .... but i'm no star in this what is R2?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 8197817
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 Comment

by:fapim
ID: 8197858
thanks again bruintje.
0
 
LVL 13

Expert Comment

by:cri
ID: 8197958
0
 

Author Comment

by:fapim
ID: 8202087
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8202208
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
 

Author Comment

by:fapim
ID: 8202293
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
 
LVL 13

Expert Comment

by:cri
ID: 8205914
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
 
LVL 13

Expert Comment

by:cri
ID: 8205933
....basepoints in the same _series_ for easier plotting.

And I think you must write ÍNDICE
0
 
LVL 13

Expert Comment

by:cri
ID: 8205939
very funny. INDICE with i agudo (?)
0
 

Author Comment

by:fapim
ID: 8209541
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 Comment

by:fapim
ID: 8210574
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
 
LVL 13

Expert Comment

by:cri
ID: 8212795
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question