Coefficients from trendline

Dear Excel Gurus,

I am wondering if I can somehow pull the coefficients from the trendline 
equation and display them in cells separately, e.g if the equation is

y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 - 
1.56030E-03x + 6.86378E+01

I want the coefficients to be displayed in separate cells

-5.21250E-21
2.35760E-17
7.99900E-13
-4.69050E-08
-1.56030E-03
6.86378E+01

I am quite sure this can be done, since these coefficients are a 
calculation, but how to do that? Correct me if I am wrong.

Thanks in advance.

Regards,
Excel User 


0
12/4/2007 1:32:17 PM
excel 39879 articles. 2 followers. Follow

4 Replies
781 Views

Similar Articles

[PageSpeed] 14

You can use LINEST as explained in
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

However, looking at the values of the first three coefficients, I doubt that 
they are meaningful
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"aberdonian" <pochta007-spam@yahoo.com> wrote in message 
news:ukk$YonNIHA.4880@TK2MSFTNGP03.phx.gbl...
> Dear Excel Gurus,
>
> I am wondering if I can somehow pull the coefficients from the trendline 
> equation and display them in cells separately, e.g if the equation is
>
> y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 - 
> 1.56030E-03x + 6.86378E+01
>
> I want the coefficients to be displayed in separate cells
>
> -5.21250E-21
> 2.35760E-17
> 7.99900E-13
> -4.69050E-08
> -1.56030E-03
> 6.86378E+01
>
> I am quite sure this can be done, since these coefficients are a 
> calculation, but how to do that? Correct me if I am wrong.
>
> Thanks in advance.
>
> Regards,
> Excel User
> 


0
bliengme5824 (3040)
12/4/2007 1:53:45 PM
Thanks Bernard. This was very helpful. I agree the values are very small, 
but if you multiply them by 18000 in the power of 5, 4 and 3 accordingly, 
then they are meaningful...

Cheers,
Excel User

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:Os0WY0nNIHA.4948@TK2MSFTNGP02.phx.gbl...
> You can use LINEST as explained in
> http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
>
> However, looking at the values of the first three coefficients, I doubt 
> that they are meaningful
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "aberdonian" <pochta007-spam@yahoo.com> wrote in message 
> news:ukk$YonNIHA.4880@TK2MSFTNGP03.phx.gbl...
>> Dear Excel Gurus,
>>
>> I am wondering if I can somehow pull the coefficients from the trendline 
>> equation and display them in cells separately, e.g if the equation is
>>
>> y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 - 
>> 1.56030E-03x + 6.86378E+01
>>
>> I want the coefficients to be displayed in separate cells
>>
>> -5.21250E-21
>> 2.35760E-17
>> 7.99900E-13
>> -4.69050E-08
>> -1.56030E-03
>> 6.86378E+01
>>
>> I am quite sure this can be done, since these coefficients are a 
>> calculation, but how to do that? Correct me if I am wrong.
>>
>> Thanks in advance.
>>
>> Regards,
>> Excel User
>>
>
> 


0
12/4/2007 1:59:53 PM
In microsoft.public.excel on Tue, 4 Dec 2007, excel user 
<pochta007-spam@yahoo.com> wrote :
>
>>>
>>> I am wondering if I can somehow pull the coefficients from the trendline
>>> equation and display them in cells separately, e.g if the equation is
>>>
>>> y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 -
>>> 1.56030E-03x + 6.86378E+01
>>>
>>> I want the coefficients to be displayed in separate cells
>>>
>>> -5.21250E-21
>>> 2.35760E-17
>>> 7.99900E-13
>>> -4.69050E-08
>>> -1.56030E-03
>>> 6.86378E+01
>>>
>Thanks Bernard. This was very helpful. I agree the values are very small,
>but if you multiply them by 18000 in the power of 5, 4 and 3 accordingly,
>then they are meaningful...

??

(18000*-5.21250E-21)^5 = -7.271*10^-81 by my calculation - so even less 
meaningful...
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
12/4/2007 5:53:26 PM
Set the optional 4th parameter of LINEST to TRUE and use the standard errors 
of the coefficients (2nd line) to evaluate their significance.  Loosely, the 
coefficient should be at least twice its standard error.  More precisely, use 
TDIST(coef/stdErr,df) to get a p-value (should be small, say <0.05).  If 
these conditions do not hold and you still believe that the higher power 
terms are real, then you need more data (probably spread over a much wider 
range) to get decent estimates.

Jerry

"excel user" wrote:

> Thanks Bernard. This was very helpful. I agree the values are very small, 
> but if you multiply them by 18000 in the power of 5, 4 and 3 accordingly, 
> then they are meaningful...
> 
> Cheers,
> Excel User
> 
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
> news:Os0WY0nNIHA.4948@TK2MSFTNGP02.phx.gbl...
> > You can use LINEST as explained in
> > http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
> >
> > However, looking at the values of the first three coefficients, I doubt 
> > that they are meaningful
> > best wishes
> > -- 
> > Bernard V Liengme
> > Microsoft Excel MVP
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "aberdonian" <pochta007-spam@yahoo.com> wrote in message 
> > news:ukk$YonNIHA.4880@TK2MSFTNGP03.phx.gbl...
> >> Dear Excel Gurus,
> >>
> >> I am wondering if I can somehow pull the coefficients from the trendline 
> >> equation and display them in cells separately, e.g if the equation is
> >>
> >> y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 - 
> >> 1.56030E-03x + 6.86378E+01
> >>
> >> I want the coefficients to be displayed in separate cells
> >>
> >> -5.21250E-21
> >> 2.35760E-17
> >> 7.99900E-13
> >> -4.69050E-08
> >> -1.56030E-03
> >> 6.86378E+01
> >>
> >> I am quite sure this can be done, since these coefficients are a 
> >> calculation, but how to do that? Correct me if I am wrong.
> >>
> >> Thanks in advance.
> >>
> >> Regards,
> >> Excel User
> >>
> >
> > 
> 
> 
> 
0
post_a_reply (1395)
12/4/2007 9:34:01 PM
Reply:

Similar Artilces:

spearman correlation coefficient
does anybody have an example of spearman correlation coefficent calculating EXCEL file with the siginifcant values at 90%? A quick Google search (using "Spearman correlation coefficient Excel") brings up several hundred possibilities, including Calculating Correlation with the Excel Spreadsheet Program http://www.mnstate.edu/wasson/ed602calccorr.htm In article <646936BA-D224-456D-938F-37F40F40AD3F@microsoft.com>, =?Utf-8?B?TWFyY2VsIExhYm9udMOp?= <Marcel Labonté@discussions.microsoft.com> wrote: > does anybody have an example of spearman correl...

Trendline Types
I am new to trendlines. I am using an xy scatter graph. Is there rule for when to use the different types of trendlines? I presently use a Power Type, but I am not sure if this is giving me a true tend. Thanks Dave - Do you have any physical basis to use one over another? In other words, if it's a physical phenomenon you're plotting, is there some theoretical relationship that describes the phenomenon? If so, use the trendline of that form, or transform the data until the model matches one of the available types. If you want a nice-looking, smooth fit, you could use a poly...

Logarithmic scale + trendlines
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I found a serious bug/mistake in Excel Trendline addition: <br> Given a linear correlation, plotted as XY (Scatter) spanned over several magnitudes. Adding a linear trendline works fine. However, changing the linear scale to logarithmic will not change the trendline to logarithmic, consequently showing the correlation in a completely wrong way. <br> I checked it with previous Win version of Excel (2003) that works fine and changes the trendline according to scale. However, Excel 2007 (Win) has the same ...

graph trendlines
hi, when I add trend lines, they are invisible. however they exist.... any idea? Thanks Perhaps the color of the trendline is set to the same color as your chart background. Look for any small breaks in the normal data curve and pass the cursor over one of these, or otherwise just sweep the cursor around until a pop-up indicates you are on a Trandline........then Right-click > Format trendline > Paterns Tab > Automatic............. Vaya con Dios, Chuck, CABGx3 "kyrbi" <kyrbi@datoeternietoe.com> wrote in message news:uq3Yd.34730$oK.3380323@phobos.telenet-ops....

How do I stop trendline from touching the y-axis?
I have plotted a few points in a scatter graph and need to add a linear trendline, however everytime I do, it extends the trendline to the Y-Axis, which makes my plots unclear and very bunched up. I want to be able to add a trendline without it touching the y-axis. Does the leftmost point have an X value of zero? Did you choose the option to force the fit to pass through the origin? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Marco Couch" <Marco Couch@discussions.microsoft.com> wrote in messag...

Coefficients from trendline
Dear Excel Gurus, I am wondering if I can somehow pull the coefficients from the trendline equation and display them in cells separately, e.g if the equation is y = -5.21250E-21x5 + 2.35760E-17x4 + 7.99900E-13x3 - 4.69050E-08x2 - 1.56030E-03x + 6.86378E+01 I want the coefficients to be displayed in separate cells -5.21250E-21 2.35760E-17 7.99900E-13 -4.69050E-08 -1.56030E-03 6.86378E+01 I am quite sure this can be done, since these coefficients are a calculation, but how to do that? Correct me if I am wrong. Thanks in advance. Regards, Excel User You can use LINEST as explained i...

Excel Trendline Formula in Scientific Format
For some reason, the when I insert a trend line into a graph, the formula comes up with one part of it in scientific format (y = 2750.2x2 - 64597x + 2E+06). I konw the last expression in the formula is $2,000,000 but this is rounded- and I need to know what the exact amount is. I have tried extending the formula box, but that doesn't work. Can anyone help me? Thanks in advance. Right click on the formula and format to scientific notation with 14 decimal places. Alternately, you could use =LINEST(ydata,xdata^{1,2}) although in it has the potential to lose accuracy in Excel ...

how i calculate pearson correlation coefficient for excel graph
how do I calculate the pearson correlation coefficient for on microsoft excel for a grapg of information. If it is not possible is there another program that can do this for me? In a cell in the worksheet, enter this formula =CORREL({y range},{x range}) where {x range} and {y range} are the ranges occupied by the X and Y values in the sheet. You can select them by clicking and dragging while you are entering the formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "university student that is lost" <univers...

Curve fit coefficient
I know that there is a LINEST function to return the coefficient of best linear curve fit However I would like to know if there is anyway to return the coefficients of, say , a quadratic curve fit? Or if there is anyway to manual type in algorithm that excel use in polynomial curve fit? Oh and I would like to have a 0 y-intercept Thanks On Mon, 12 Apr 2010 05:58:02 -0700, Kipi <Kipi@discussions.microsoft.com> wrote: >I know that there is a LINEST function to return the coefficient of best >linear curve fit >However I would like to know if there is anyway to re...

Trendline Values
How do you display the values of a trendline on your chart? I used the add trendline feature within the chart and would like to see the exact values it plotted -- the only items I can view are the R-squared and the equation, but no values. Thanks! [This followup was posted to microsoft.public.excel.charting with an email copy to Kristin. Please use the newsgroup for further discussion.] There's no easy way to get what you want from the trendline data. If the relationship is linear (i.e., y=a*x+b), it would be a lot simpler to use the TREND worksheet function. Suppose the X va...

Is it possible to generate a chart from a formula. or: Can I define and plot my own Trendline?
I'd like to generate a circle on a chart by entering the radius of the circle into a cell. Thanks in advance for any help, Greg Suppose the radius is in cell A1. Then, in B1 enter the formula =ROW()-1. In C1 enter =B1/180*PI(), in D1, =$A$1*COS(C1), and in E1 =$A$1*SIN(C1). Copy row 1 to 2:361. Plot D1:E361 in a XY Scatter chart. Once done, adjust the chart so that the x and y axis have the same min/max values. Also, select the plot area and extend it as close to the chart area as possible. Finally, select any cell. Then, hold down SHIFT and select the chart. Now, select...

Trendline coefficients
I have fit a series of data using a 5th order polynomial from the trendline dialog. Using the coefficients displayed in the equation, the curve is not faithfully reconstituted. Is it possible to export the trendline coefficients to arbitrary significant figures? Select the trendline equation on the greaph and format the number of significant figures "MrUniverseman" wrote: > I have fit a series of data using a 5th order polynomial from the trendline > dialog. Using the coefficients displayed in the equation, the curve is not > faithfully reconstituted. Is it po...

trendlines #8
I am fairly new at this. When plotting fairly simple XY scattergrams I have noticed that when y decreases at an increasing rate, the Power trendline Excel yields fits the data with a very high r-square. But when y decreases at a decreasing rate, the resulting trendline fits poorly and the r-square is low. How do I get the trendline (and resulting formula) to "invert" to better fit the data? Thanks. I suspect you have checked that the trend line option "Set intercept" =0, forcing the trendline to go through (0,0). If so, uncheck, and even Excel will figure out a...

Correlation coefficient (r)
Hi folks, This question is similar to Elizabeth Brown's, but with a slight twist. I am also looking to calculate r ..from a exponential function plotted as a log-lin scatter graph: I posted last week (5 August) about a way of calculating the correlation coefficient from a log-lin plot that is tolerant of deleted/mssing data, but the proposed solution (CORREL array-entered) doesn't seem to work. Unless I have overlooked something? To restate, I have a Log-lin plot based on data in columns A and C. Column C contains the y-values. Columns A and B just contain data (A1:A4 = 100,150,200,...

Excel: Can I force a linear trendline through the origin?
Yes, open at the Option tab when you make (or format) a trendline There is a text box to set intercept to any value (including 0) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill" <Bill@discussions.microsoft.com> wrote in message news:B0487508-C3C9-46C9-A7E3-EA42B60016E2@microsoft.com... > I have done that, but it is not working. Perhaps it is because I am plotting a log-log graph and therefore there is no actual zero value for the y-axis. But I need the line to pass through the origin, and because the data covers severa...

Trendlines
Does anyone know how to choose the correct trendline for a data set? I have plotted a data set in Excel 2007 (located at the following link) http://www.mediafire.com/?sharekey=a6c026861bdd9f9ad2db6fb9a8902bda, but I don't know how to choose the correct trendline from the options Excel provides. The 'correct' trendline depends on the data. If I weight 2 bags of sugar, then 4 bags, then 5 I would expect the trend to be linear. But if I measured the area of circles of different radii, then I would need to use a second order polynomial with zero intercept. And so on. What is you da...

Logarithmic trendline unavailable
I have an X-Y scatterplot and would like to plot a trendline. The physics of the problem imply a logarithmic decay. When I try to add a trendline, the logarithmic option is not highlighted and hence unavailable to choose. I have similar graphs on a different worksheet page where the logarithmic trendline is available and works fine. Any idea why the logarithmic trendline is unavailable and how to address? I can change the x-axis to a log scale and Lotus 123 will provide me with logarithmic trendlines for the exact same data, so the problem does not appear to be caused by invalid da...

Excel option to store trendline's coefficients in cells for use
I believe it will be useful to add an option to Excel that when adding a tendline to a graph, Excel could ask in which cells it will store the coefficients and exponents in the resulting trendline. In this way, the user can use those coefficients in other calculations. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and the...

Trendline Question- Graph with 2 Y Axis
I have a graph which uses a primary & a secondary Y axis. The trendline works fine for the series which uses the primary Y axis, but the series using the secondary Y axis adds a trendline based on the primary Y axis. Am I doing something wrong? thnx ...

Power curve coefficients
I am looking for help on how to automatically extract the constants generated by doing a power curve fit to data. I know how to make the equation of a power curve be displayed, but I would like to extract the values directly. All help appreciated. -- BGHoneywell ------------------------------------------------------------------------ BGHoneywell's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26141 View this thread: http://www.excelforum.com/showthread.php?threadid=394694 BGHoneywell - > I am looking for help on how to automatically extract the constan...

trendline coefficient accuracy
I am trying to establish the polynomial coefffiecients for a fuel consumtption curve for one of our ships. I have plotted the recorded consumption data from the ship and trended them with a 3rd power polynomial. the curve folllows the data well, however, the set of genertated coefficients, which when put back into the graph to generte test curve that in no way refelct the original tend line. and can't be used to forcatst the fuel budget. I must be doing something stupid. Thanks, Simon. On Thu, 28 Jul 2005 14:41:03 -0700, "Simon Y" <Simon Y@discussions.micros...

Trendlines #5
Hi All, I want to add a second Trendline to my chart. Joining only three markers. How do I highlight just three markers and add the trendline. I know how to draw a line manually. Thanks, kfh. You need to add a new series with just the data for those three points. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ k f h wrote: > Hi All, > > I want to add a second Trendline to my chart. Joining only three markers. > How do I highlight just three markers and add the trendline. I know how...

Lookings for optimal coefficient
I have a set of numbers A1:A10. I have a formula A1:A10 ~ x*B1:B10 + y*C1:C10 + z*D1:D10 How do I find the optimal set of x,y,z to give the result closest to A1:A10 for every B1:B10,C1:C10,D1:D10 I think you could use a Solver. go to tools--> add-ins--> select Solver add-in then go to tools again select Solver Target cell: where your current formulla is (x*B1:B10 + y*C1:C10 + z*D1:D10) , click in vlaue of and type in how much a1:a10 in by changing cell select where your x,y,z are located and sepreate them by coma. now if you click on solve you should get and answer. Nikki...

"Add Trendline" grayed out
I have an excel chart. I have a column with each day of the year, a column with the balance for an account for each day (up to today) and a third column that contains the desired balance for each day of the year (through the end of the year). I created an area chart that shows the daily balance. I want a trendline that shows the expected balance for each day. This will allow me to visually see where I am, and if I am above or below the expected level. When I try to "Add Trendline" the option is grayed out in my Chart menu. Why? I have this on a similar chart in another sp...

Trendline when data points are missing
I have data where for some of the series there is data in each "category" and in other series there is some missing data. Here's the example: Fund Type Fund Company One Year Return Target 2050 Principle 12.2 Target 2050 TRP 11.3 Target 2045 Principle 11.1 Target 2040 Principle 10.8 Target 2040 TRP 9.4 When I chart this I get a line for all of the "Principle" data because it has a data point for all three "Fund Types". I only get...