Trendline equation not giving correct results

I was trying to form a trendline with following data :
Y	X
1.128	-20
1.128	-15
1.128	-10
1.128	-6
1.111	-5
1.084	0
1.056	5
1.0276	10
1	15
0.971	20
0.944	25
0.9144	30
0.884	35
0.8524	40
0.82	45
0.788	50

After plotting the chart, I added trendline (Polynominal 
with order 6) alongwith its equation which read as below : 

y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 
0.0318x + 1.1429 and R2 = 0.9998

The trendline in the chart neatly fitted over the curve. 
However, when I used above equation and fed in same values 
of X as shown above the computed results are entirley 
different from the data as shown in Y above. Following is 
the output of the equation for various values of X

Y1	X1
286.379	-20
85.6855	-15
18.9109	-10
4.37777	-6
3.03003	-5
1.1429	0
1.09953	5
0.8749	10
0.69402	15
1.9069	20
12.6135	25
60.0389	30
209.658	35
591.071	40
1430.63	45
3090.8	50

When I plotted above data by adding another series it 
gives entirely different curve.

I am unable to understand above. 

Anybody can help me understanding above and how to rectify 
above problem.

Thanks
Sudhanshu





0
anonymous (74725)
1/9/2004 1:14:36 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
671 Views

Similar Articles

[PageSpeed] 6

Double-click the equation of the polynomial on the graph.  From the 
Number tab, select a format of 'Number' and set the number of decimals 
to 15.

That said, consider plotting the data first.  You will see that the use 
of a sixth order polynomial is unwarranted, and that a graph is a 
probably best described by two straight lines.

-- 
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <0aa701c3d6b2$875f2c30$a401280a@phx.gbl>, 
anonymous@discussions.microsoft.com says...
> I was trying to form a trendline with following data :
> Y	X
> 1.128	-20
> 1.128	-15
> 1.128	-10
> 1.128	-6
> 1.111	-5
> 1.084	0
> 1.056	5
> 1.0276	10
> 1	15
> 0.971	20
> 0.944	25
> 0.9144	30
> 0.884	35
> 0.8524	40
> 0.82	45
> 0.788	50
> 
> After plotting the chart, I added trendline (Polynominal 
> with order 6) alongwith its equation which read as below : 
> 
> y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 
> 0.0318x + 1.1429 and R2 = 0.9998
> 
> The trendline in the chart neatly fitted over the curve. 
> However, when I used above equation and fed in same values 
> of X as shown above the computed results are entirley 
> different from the data as shown in Y above. Following is 
> the output of the equation for various values of X
> 
> Y1	X1
> 286.379	-20
> 85.6855	-15
> 18.9109	-10
> 4.37777	-6
> 3.03003	-5
> 1.1429	0
> 1.09953	5
> 0.8749	10
> 0.69402	15
> 1.9069	20
> 12.6135	25
> 60.0389	30
> 209.658	35
> 591.071	40
> 1430.63	45
> 3090.8	50
> 
> When I plotted above data by adding another series it 
> gives entirely different curve.
> 
> I am unable to understand above. 
> 
> Anybody can help me understanding above and how to rectify 
> above problem.
> 
> Thanks
> Sudhanshu
> 
> 
> 
> 
> 
> 
0
1/9/2004 3:06:26 PM
Sudhanshu -

Did you notice the lack of significant digits in the trendline equation? 
6E-07? Barely one significant digit, with almost a ten percent error 
(6�0.5).

First of all, put the X to the left of Y. This matches the way Excel 
naturally assigns X and Y to a series, so you won't have to fix it. Then 
make the chart, add the trendline, and double click on the trendline. 
Click on the Number tab, click on Scientific, and select 14 decimal 
places. Now you have enough precision to use the coefficients to compute 
your own values.

If you don't want to do all the copy-pasting required to get these 
coefficients into the worksheet, Dave Braden has posted code to do this 
for you:
http://groups.google.com/groups?selm=dbraden-1C662A.14054705032003%40msnews.microsoft.com

If you want 2� more from me, keep reading. It looks like your data is a 
sloping curve that hits a plateau at Y=1.128. If I fit a 2nd order 
polynomial to the points with Y<0.128, I get a better R-squared. In 
general, higher order fits are only good for interpolation (IMHO); 
nobody can convince me there's any physical significance to higher order 
terms than 2nd order. In fact, a linear fit of these points gives me a 
better R-squared than the 6th order fit of the entire data set, although 
you can see a slight curvature by eye. The apparent need for excess 
orders in the poly fit is required to account for the discontinuity in 
the data, a transition from one mechanism of variation to another.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Sudhanshu wrote:

> I was trying to form a trendline with following data :
> Y	X
> 1.128	-20
> 1.128	-15
> 1.128	-10
> 1.128	-6
> 1.111	-5
> 1.084	0
> 1.056	5
> 1.0276	10
> 1	15
> 0.971	20
> 0.944	25
> 0.9144	30
> 0.884	35
> 0.8524	40
> 0.82	45
> 0.788	50
> 
> After plotting the chart, I added trendline (Polynominal 
> with order 6) alongwith its equation which read as below : 
> 
> y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 
> 0.0318x + 1.1429 and R2 = 0.9998
> 
> The trendline in the chart neatly fitted over the curve. 
> However, when I used above equation and fed in same values 
> of X as shown above the computed results are entirley 
> different from the data as shown in Y above. Following is 
> the output of the equation for various values of X
> 
> Y1	X1
> 286.379	-20
> 85.6855	-15
> 18.9109	-10
> 4.37777	-6
> 3.03003	-5
> 1.1429	0
> 1.09953	5
> 0.8749	10
> 0.69402	15
> 1.9069	20
> 12.6135	25
> 60.0389	30
> 209.658	35
> 591.071	40
> 1430.63	45
> 3090.8	50
> 
> When I plotted above data by adding another series it 
> gives entirely different curve.
> 
> I am unable to understand above. 
> 
> Anybody can help me understanding above and how to rectify 
> above problem.
> 
> Thanks
> Sudhanshu
> 
> 
> 
> 
> 

0
1/9/2004 4:03:02 PM
>-----Original Message-----
>I was trying to form a trendline with following data :
>Y	X
>1.128	-20
>1.128	-15
>1.128	-10
>1.128	-6
>1.111	-5
>1.084	0
>1.056	5
>1.0276	10
>1	15
>0.971	20
>0.944	25
>0.9144	30
>0.884	35
>0.8524	40
>0.82	45
>0.788	50
>
>After plotting the chart, I added trendline (Polynominal 
>with order 6) alongwith its equation which read as 
below : 
>
>y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 
>0.0318x + 1.1429 and R2 = 0.9998
>
>The trendline in the chart neatly fitted over the curve. 
>However, when I used above equation and fed in same 
values 
>of X as shown above the computed results are entirley 
>different from the data as shown in Y above. Following is 
>the output of the equation for various values of X
>
>Y1	X1
>286.379	-20
>85.6855	-15
>18.9109	-10
>4.37777	-6
>3.03003	-5
>1.1429	0
>1.09953	5
>0.8749	10
>0.69402	15
>1.9069	20
>12.6135	25
>60.0389	30
>209.658	35
>591.071	40
>1430.63	45
>3090.8	50
>
>When I plotted above data by adding another series it 
>gives entirely different curve.
>
>I am unable to understand above. 
>
>Anybody can help me understanding above and how to 
rectify 
>above problem.
>
>Thanks
>Sudhanshu
>
>
>right click the equation text box on the chart and this 
gives "format data label" choose "number" and select a 
high value of decimal points (I use 10) this will give you 
a much more accurate curve. If it isn't accurate enough go 
for more decimal places. If it's really complicated and a 
sixth order poly isn't enough cut the graph into sections 
each with its own polynomial and use =if (between one 
value and another one poly ; the other). 

Mike G
>
>
>.
>
0
anonymous (74725)
2/5/2004 3:51:17 PM
You should consider whether you might be seriously overfitting the data. 
  There is essentially no improvement in the fit over a cubic polynomial.

Unless Y is known to more decimal places, you would do even better with 
a piecwise linear equation joined between -5 and -6.  The higher order 
polynomials are trying with limited success to model that sudden shift 
from essentially linear with a slope of -0.0058... to linear with a 
slope of 0.

Jerry

MikeG wrote:

>>-----Original Message-----
>>I was trying to form a trendline with following data :
>>Y	X
>>1.128	-20
>>1.128	-15
>>1.128	-10
>>1.128	-6
>>1.111	-5
>>1.084	0
>>1.056	5
>>1.0276	10
>>1	15
>>0.971	20
>>0.944	25
>>0.9144	30
>>0.884	35
>>0.8524	40
>>0.82	45
>>0.788	50
>>
>>After plotting the chart, I added trendline (Polynominal 
>>with order 6) alongwith its equation which read as 
>>
> below : 
> 
>>y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 
>>0.0318x + 1.1429 and R2 = 0.9998
>>
>>The trendline in the chart neatly fitted over the curve. 
>>However, when I used above equation and fed in same 
>>
> values 
> 
>>of X as shown above the computed results are entirley 
>>different from the data as shown in Y above. Following is 
>>the output of the equation for various values of X
>>
>>Y1	X1
>>286.379	-20
>>85.6855	-15
>>18.9109	-10
>>4.37777	-6
>>3.03003	-5
>>1.1429	0
>>1.09953	5
>>0.8749	10
>>0.69402	15
>>1.9069	20
>>12.6135	25
>>60.0389	30
>>209.658	35
>>591.071	40
>>1430.63	45
>>3090.8	50
>>
>>When I plotted above data by adding another series it 
>>gives entirely different curve.
>>
>>I am unable to understand above. 
>>
>>Anybody can help me understanding above and how to 
>>
> rectify 
> 
>>above problem.
>>
>>Thanks
>>Sudhanshu
>>
>>
>>right click the equation text box on the chart and this 
>>
> gives "format data label" choose "number" and select a 
> high value of decimal points (I use 10) this will give you 
> a much more accurate curve. If it isn't accurate enough go 
> for more decimal places. If it's really complicated and a 
> sixth order poly isn't enough cut the graph into sections 
> each with its own polynomial and use =if (between one 
> value and another one poly ; the other). 
> 
> Mike G
> 
>>
>>.
>>
>>

0
post_a_reply (1395)
2/6/2004 12:25:46 AM
Reply:

Similar Artilces:

Trendline error???
Has anybody else had this problem? If one uses a 6 order polynomial as trendline and then s/he enters manually in an other column the same equation taken from the displayed trendline equation on the graph , the original trendline is different from the one manually entered. Excel plots the two different curves having the same equation!!! Please see an example at : http://www.geocities.com/audax/error.xls Thank you for the help. Gene Disregard the previous message. The problem is due to the rounding off. The trendline calculation is OK. Gene See my website for the use of LINEST to get coef...

Is this UTF-8 regular expression semantically correct?
BYTE_ORDER_MARK [0\xEF][0\xBB][0\xBF] ASCII [\x0-\x7f] U2 [\xC2-\xDF][\x80-\xBF] U3 [\xE0][\xA0-\xBF][\x80-\xBF] U4 [\xE1-\xEC][\x80-\xBF][\x80-\xBF] U5 [\xED][\x80-\x9F][\x80-\xBF] U6 [\xEE-\xEF][\x80-\xBF][\x80-\xBF] U7 [\xF0][\x90-\xBF][\x80-\xBF][\x80-\xBF] U8 [\xF1-\xF3][\x80-\xBF][\x80-\xBF][\x80-\xBF] U9 [\xF4][\x80-\x8F][\x80-\xBF][\x80-\xBF] U {ASCII}|{U2}|{U3}|{U4}|{U5}|{U6}|{U7}|{U8}|{U9} > BYTE_ORDER_MARK [0\xEF][0\xBB][0\xBF] > ASCII [\x0-\x7f] > U2 [\xC2-\xDF][\x80-\xBF] > U3 [\xE0][\xA0-\xBF][\x80-\xBF] >...

insert worksheet gives me 3 duplicate sheets not one
I do insert worksheet from the Insert menu, I only have one tab highlighted and I get 3 duplicate worksheets as opposed to an new worksheet. Also when I open excel if get the worksheet i expect but if I open a new sheet I get one with some spurious numbers in it. I've tried making sure that there's nothing in XLstart or in the file line under options>general>at startup, open files in.. that's blank. So I would have thought I was back to the default settings but this doesn't seem to be the case. I have tried doing a re-install from the office disc and doing a ch...

Add Trendline to Pivot Table Graph
I have a list of survey responses and I have created a Pivot Table with Month as the Row Heading and Count of Respondent as Data. I have then created a Pivot Table chart. I want to show a trendline but when I select the columns and right click for Trendline, as I would normally, the option is greyed out. How do I create a trendline for the pivot table category columns? If Add Trendline is disabled with a pivot chart (I don't know, I rarely make pivot charts), you could plot the data in a regular chart, then add your trendline. Pivot Tables, Pivot Charts, and Real Charts...

Trendlines in graphs
Hi all! I have an interesting question. I am ploting a graph where the X axis is by months and the Y axis is the number of parts produced. Some months we do not produce any parts; therefore, the Y axis is "0" at that time. When I put a trend line on the chart, it sees the "0" as a computable data point. A line is then generated but is off from the true trend. The true trend can be generated if I remove all the non producing months. Is there a way to specify specific groups of cells in a column that you want to use in a trendline analysis--sort of hop scotch ...

summing results of multiple vloopups
Can anyone help with the following vloopup formula? =IF(ISNA(VLOOKUP($A4,RANGEA,3,FALSE)),0,VLOOKUP($A4,RANGEA,3,FALSE))+IF(ISNA(VLOOKUP($A4,RANGEB,3,FALSE)),0,VLOOKUP($A4,RANGEB,3,FALSE))+IF(ISNA(VLOOKUP($A4,RANGEC,3,FALSE)),0,VLOOKUP($A4,RANGEC,3,FALSE)) If there is a value in all three ranges it returns the currect amount. Here are the problems: - If RANGEA is missing a value it does not add RANGEB and RANGEC. - It RANGEA and RANGEC are missing values it adds RANGEB to a value from the cell above. Any suggestions? Thanks Nothing wrong with the formula although it ...

Can someone give me a script please
Hi Could someone give me a script to do the following please? In the Customer table there are over 500 records. Most of them have 3 digit numbers eg 423 The last 50 or so have 6 digit numbers eg 000475 I want to change all the 3 digit ones to add 3 leading 0's to the number ie to make 423 into 000423 Thanks Gaz, This should get you were you wanna go: UPDATE Customer SET AccountNumber = '000' + AccountNumber WHERE LEN(AccountNumber) = 3 -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ...

weighted trendline
Can anyone tell me if it possible to weight a linear trendline in excel? Like a calibration line where you can weight by 1/x where x is the variance at each point on the line. Hi, I don't believe that can be done with anything built into the trendline chart feature, but you can use the trendline formula in the spreadsheet and then apply your weighting to it. Then plot that rather than the built-in trendline. You can get the formula of the trendline from the chart by choosing to display it or in the spreadsheet by using the LINEST function or the related SLOPE and INTERCEPT func...

Excel currency format does not translate correctly in a Word merge
When I merge an Excel file with various fields/formats, the currency formats [seven different columns with currency] the Word file displays 70 instead of $70.00. What do I need to do and in which file? "ILoveMyCorgi" <ILoveMyCorgi@discussions.microsoft.com> wrote in message news:FB51D073-B13F-47C5-B77C-8C7007C62C3F@microsoft.com... > When I merge an Excel file with various fields/formats, the currency > formats > [seven different columns with currency] the Word file displays 70 instead > of > $70.00. What do I need to do and in which file? Exc...

vat adds up correctly accross spreadsheet but down the columns whe
I have a very basic spread sheet which adds VAT to various cost figures. I have a total VAT cell below the VAT columns. When adding the columns together the answer is normally out by 1p less. Example: Cost VAT £317.00 55.48 £317.00 55.48 Total VAT should be £110.96 but Excel calculates at £110.95 The above calculation may be a bad example but the above occurs when the VAT is rounded up or down against an individual cost figure. So why is the Total different if the sum is just the adding up of the VAT individual totals? Thanks because 17.5% ...

Booklet used to print correctly #2
My company just updated to MS Office 2007. I have a small booklet in Publisher that used to print perfectly, now pages are upside down. I’ve read previous posts, even tried smaller paper. Ahhh, please help… When you say "small", do you mean 5.5 x 4.25? The service pack corrected this print error. Description of 2007 Microsoft Office Suite Service Pack 2 (SP2) and of Microsoft Office Language Pack 2007 SP2 http://support.microsoft.com/kb/953195 -- Mary Sauer http://msauer.mvps.org/ "Pam" <Pam@discussions.microsoft.com> wrote in message news:17339...

IE8 BUG - CSS zoom
Open a page with an image and a map with areas. Use the Developer tools to add the css style="zoom:50%". Higlight a map area tag in the tool and you will see that the areas are not in the correct position or the correct size. ---------------- 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 then click "I Agre...

Increaseing Precision in polynomial trendline equations
How can I increase the precision in Excel's "Display Equation" option for trendlines? I am trying to fit a polynomial regression (trendline) to a X, Y scatterplot I have created. Excel's built-in trendline function shows a good fit (R2=0.9999) for a 6th order polynomial, however, If I re-plot the data using the coefficients from the "Display equation" option, the data diverge significantly. Apparently, for high order polynomials, you need to have a high level of precision in your coefficients (many decimal places accurate) in order to actually re-plot the same ...

Trendline
How can I create multiple trendlines on one graph i.e I have a graph with 5 years data and I want to show a linear trendline for each year on the same line? You need each year plotted as a separate data series, then follow Mike's advise best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Carlo" <Carlo@discussions.microsoft.com> wrote in message news:3F847B4B-9C32-464A-82F9-001A340A67FB@microsoft.com... > How can I create multiple trendlines on one graph i.e I have a graph with > 5 > years data and I wa...

editing data using a form
Hi everyone, I'm thinking what should my approach be, to do a simple data edit correctly. I've got a table with 1500 jobs, User can scroll throufgh the list, filter, etc, etc, and I want to enable him to edit details of a particular job. Because the data is quite vulnerable and users are quite prone to type sth in or change sth without noticing, I wouldn't want the details to be changed whenever a user changes them on a form and moves a focus to another control. IS there any simple way to achieve that that I just don't know about? Cause if I just make a form.recordsource bein...

Powerpoint crashes abruptly when using RMB for spelling correction on pasted text
3 out of 5, if I use RMB to open the context menu on a word that is not recognized by spell checker to correct it or add it to dictionary, Powerpoint 2003 crashes abruptly IF the text was pasted from another source (any) rather than filled in by typing. I suspect that pasted texts may contain some hidden markup that may create problems to spell checker. The point is: is there a way to see what is REALLY inside a PPT text box? Dario de Judicibus ...

quickly give GP users access to mod screens / reports
Hi there, Is there a method to quickly give users that have access to specific screens / reports, only access to the mod versions of all those resources? An article in Knowledge Base describes this - see Q20 https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYKKQRRUZVRPSSXNSNWTQZRSXSPOKVWULOKZNVQSQLKPXQXQTZ However the solution described here is very cumbersome, and requires that you first investigate which specfic users have access to which specific resources. For instance, one client has 100+ users at 10+ companies, with 200+ mod's. To give all users access to mo...

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

correct total column in flitered list
This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C75DB4.A3C658D0 Content-Type: text/plain; charset="windows-1256" Content-Transfer-Encoding: quoted-printable correct total column in filtered list Dear friends, I have a list(table in 2007) as bellow Serial Item Code Import Export Remain =20 1 10 20 =20 20 =20 2 20 =20 10 10 =20 3 10 30 =20 40 =20 4 30 10 =20 50 =20 5 20 =20 20 30 ...

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

Remove Trendline
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: imap How do I remove a trendline? This is very frustrating. <br><br>Even at 200% zoom there is no way to click on the trendline. The cursor remains a crossed arrow. If I right click on the data line, there is no choice for removing a trendline, only for adding new trendlines. <br><br>Robert Rose So I see I posted this to the wrong Forum. Shows how frustrated I am. Hi Robert, I can simply click on the trendline, and it gets highlighted. You have to be very careful...

Trendlines
Can anyone help? I what to add a logarithmic trendline but the option is greyed out and I can't seem to find where the addin might be. I am working with Excel business 2000. There is no add-in. Sounds as if the data is not amenable to a log fit.Are there negative values? Is the x-axis numeric? Bernard "Gee" <anonymous@discussions.microsoft.com> wrote in message news:E66D16D7-BAC5-454A-9827-59BE6BFB8447@microsoft.com... > Can anyone help? I what to add a logarithmic trendline but the option is greyed out and I can't seem to find where the addin might be. I am worki...

Placing Trendlines Behind Data Points
Is there a way to get the trendline to show behind the points? I have an XY (scatter chart using just the points (no lines). I added a trendline to get a nice parabolic curve. Normally I would use the connected option when I pick the chart type, but the resulting line does not fit as well as the trendline. Not that I know of. The trendline shows within open marker with a white background best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sloth" <Sloth@discussions.microsoft.com> wrote in message news:3D4A7967-EF8C-4B9A-9F76-E280F235E...

cannot see trendlines
I have a graph with 6 series. There is a primary and a secondary X axis. I want trendlines (moving average) on all data series. The chart will not show any. No error messages, go through procedure of adding trendline, but it just doesn't show up. Search for hidden line with cursor, doesn't show. The trendlines show up in the legend whether or not they show up on the chart. Scaling is automatic, and there is space above and below the data curve. ...

Word 2007 Equations not displaying but printing fine
I have a Word 2007 document and I have inserted some equations (Insert tab, Equations) and after I get done entering the information and click elsewhere alls I see on the page is a white box with a black border. The funny thing is when I print the equations display. If you go into the Word Options, Advanced, I have it checked to display text boxes and drawings and still nothing. any ideas? thanks! B ...