By a magnitude of at least 4. Generally a scientific format with lots of digits (14 or 15 digits) will provide sufficient resolution. What's more, the LINEST results below agree with the trendline: Here is the data and calculated Y values based on the LINEST coefficients: Here is the chart with the trendline: Here bahaa siha says: Monday, February 11, 2013 at 7:49 pm Hi I have a question regarding polynomial trend line in a scatter plot.

Error 3: Manually Transcribing Coefficients If you want to use the trendline coefficients in the worksheet, there's a better approach than manually transcribing data from the trendline formula to cells. Why is that? Was the increase in the first column really a step function (increase of 0.02 for the first four measurements, then 0.03 for three measurements, then 0.04 for five measurements, etc.) or But doing that makes the line not fit as well.

Does it lend itself to a curve fit? Do you know what is going on there and how I can remedy to this problem? If you look at the graph, the constant should be 1.45e+06. Perhaps I'm not doing this in the ideal way, but I still can't figure out why in the world the regression formula wouldn't work.

Subtracting the same date from each X value resulted in the correct coefficients. Any ideas? Also, the point with zero force looks like it's measured at a greater X than eyeball extrapolation of the last four or five points would warrant. Reply With Quote Nov 11th, 2012,01:28 PM #10 FBPER New Member Join Date Jun 2012 Posts 7 Re: Polynomial trend line giving wrong formula I have in cells H1 to N1:

uday kumar says: Tuesday, May 3, 2011 at 2:05 am Many many thanks to you jon for answering expeditiously. Can I trend part of my graph? I am not sure how well i have explained this, but if anyone could help it would be great! The usual problem when the chart trendline cannot be reproduced is that the user failed to reformat the equation to dsiplay full precision.

I don't want to use the source range, as that has values which are too high (e.g. Here is the FAQ for this forum. + Reply to Thread Results 1 to 8 of 8 Excel calculating totally wrong trend line equations Thread Tools Show Printable Version Subscribe to Peltier Tech Home About Jon Peltier About Peltier Tech Copyright and Licensing Blog Comment Policy Privacy Policy Peltier Tech Products that Create Special Charts in Microsoft Excel Recent Blog Posts Microsoft But for the documents I make I need to have excel graphs so was hoping to use the trendlines in Excel.

The Trendline displayed on the chart does not correspond to the equation displayed. The first term of the equation was dropped off. You can get away with using a line chart (particularly with a date-scale axis), so long as you understand what the categories mean. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Nov 11th, 2012,02:09 AM #2 Cindy Ellis MrExcel MVP Join Date Jun 2006 Location California Posts 1,801 Re: Polynomial

Share this:Click to share on Twitter (Opens in new window)Click to share on Facebook (Opens in new window)Click to share on LinkedIn (Opens in new window)Click to share on Google+ (Opens What sense of "hack" is involved in five hacks for using coffee filters? But the equation that the formula spits out doesn't work when I try to get the actual numbers (I need to know what the best fit output is for 24 and if a trendline seems to fit very well by visually looking and by R^2 value, then why does the trendline equation not outputs a value nearer to the sample data. 2.

The problem: While on some cases it works nicely, in one occasion I get odd results. However, the equation of the trendline is off. the line graph may reach 1000, but the trend only goes to 100). When I plot the data plugged back into this formula, it matches the trendline drawn by Excel.

I need those trendline values to know exactly where my actual datapoints fall relative to my trendline. The trendline was made using "Add Trendline" on the 4pcmm data set. –puggsoy Nov 5 '14 at 6:16 @fixer1234: Yeah, I've done this before with other data sets and I'm trying to generate the quadratic equation (ax^2 + bx + c) for a set of data using only VBA. I don't understand.

In particular, the x^4 and x^3 coefficients are only to given to one significant figure, and when you use these coefficients rounded to 1 sig fig, you will get very different It appears that excel has displayed the wrong coefficients on the chart. The long and sordid details of the error follow: 1605 -12687 1779 -13818 1886 -14663 1861 -14891 1647 -13650 1344 -11595 832 -7700 660 -6268 The first error identified was a For example, if we calculate it at an X position of 50: y = (4538.1 * 50) + (1 * 10^6) Punch this into Excel (or just a calculator) and we

As you have suggested in this post, I increased the precision of co-coefficients but even then i did not get the value near to actual value. Your cache administrator is webmaster. so the fitted y was wrong. -:) sometimes people are just habitual to handle big problems but can not silly problems! how do i get the data from the trend line accurately?

This allows you to quickly and easily create line charts and graphs from Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF People here at my new job use Excel to place trendlines on graphs and refer to the trends -- with no exploration into whether the coefficient on the slope of the Turns out -- no-one had checked the significance of that coeffcient at all. This error was obvious to the user, so it’s not particularly dangerous.

The value of the bad third order coefficient was not real small (same order as the other two) Moreover, the bad coefficient would not display at all in SP1 version once The “Second Try” was generated by changing the Trendline to logarithmic mode, then back to polynomial. This is Yet Another Reason not to use 3D charts, when will you ever learn? However, it does cause confusion.

I assume you didn't commit the first error (using a line chart). The equation I am given for this curve is that of a squared polynomial, and the R squared is 99.9% (so supposedly good fit). I'm just hoping there is a more elegant way of getting at the calculation. Is it possible to fit a trend line to only part of my data?

I make an xy chart. Five days doesn't even give you a whole week to allow you to average the effects of days of the week, and the last week of December is pretty dead. My data is shown below. Register To Reply 07-10-2012,11:58 AM #5 ChemistB View Profile View Forum Posts Forum Guru Join Date 08-05-2004 Location NJ MS-Off Ver MS 2007, 2010 Posts 11,754 Re: Excel calculating totally wrong

I do wonder why it was being funny though, all previous times it worked fine. –puggsoy Nov 6 '14 at 0:53 add a comment| Your Answer draft saved draft discarded XY Charts I described differences between XY and Line charts. There is a discontinuity in the curve, which appears as a small bump between X=0.9 and 1.0, but on a log log plot it looks like there was some slippage in This riddle could be extremely useful House of Santa Claus Developing web applications for long lifespan (20+ years) What are "desires of the flesh"?