Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
I am trying to work with trendlines, and I can match my data with a
polynominal trendline. The plotted trendline matches the data points very closely, but when I apply the formula generated to actual data, my projected values are way off. I believe the problem is that the displayed formula is rounding constants off and not displaying to the number of significant figures required to generate a workable formula. Are there any ways to change any settings to allow Excel to display more figures in the diplayed trendline formulas? Thank you in advance. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
The results will be very sensitive to how much precision you use for the
coefficients Ergo: do not use the trendline values but use the results from LINEST See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm The use formulas that point to the coefficients in the LINEST results to get the max precision of Excel best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John1791" wrote in message ... I am trying to work with trendlines, and I can match my data with a polynominal trendline. The plotted trendline matches the data points very closely, but when I apply the formula generated to actual data, my projected values are way off. I believe the problem is that the displayed formula is rounding constants off and not displaying to the number of significant figures required to generate a workable formula. Are there any ways to change any settings to allow Excel to display more figures in the diplayed trendline formulas? Thank you in advance. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
Hi,
What Bernard says is true, and is the best way to proceed, as the coefficients are placed in cells, but you can easily get Excel to display more figures for the equation on the chart simply by clicking on the equation and then increasing the number of decimals using the "Increase Decimal" toolbar button, or right-click the equation, and Format it to give more places. Dave url:http://www.ureader.com/msg/10296433.aspx |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
It looks like there is a problem when the trendline calculates the
coefficient for datasets that are way off from the origin. I found the same problem. The fitted curve looks great but the coefficients are way off. The regression algorithm does much better. If the dataset is translated around the means; i.e., use (x-xaverage) and y-yaverage), the trendline predicted values also comes out pretty good. The regression algorithm will presumably differences and perform the regression on the differences, just like the regression add-in. the fact that the trendline curve is usually pretty good especially if you go to the sixth order polynomial, means that the regression is ok, but the reporting of the coefficients when it translates back to the original dataset is in error. The same results are obtained whether in XP, 2003 or 2007. Microsoft should pay attention! "John1791" wrote: I am trying to work with trendlines, and I can match my data with a polynominal trendline. The plotted trendline matches the data points very closely, but when I apply the formula generated to actual data, my projected values are way off. I believe the problem is that the displayed formula is rounding constants off and not displaying to the number of significant figures required to generate a workable formula. Are there any ways to change any settings to allow Excel to display more figures in the diplayed trendline formulas? Thank you in advance. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
Please give an example where the trendline in 2003 gives worse results than
LINEST. Prior to 2007, the chart polynomial trendline algorithm was excelent, and such claims usually involved user error such as using a Line chart instead of a Scatter chart, or failure to display enough figures for the chart trendline coefficients. Jerry "BOS" wrote: It looks like there is a problem when the trendline calculates the coefficient for datasets that are way off from the origin. I found the same problem. The fitted curve looks great but the coefficients are way off. The regression algorithm does much better. If the dataset is translated around the means; i.e., use (x-xaverage) and y-yaverage), the trendline predicted values also comes out pretty good. The regression algorithm will presumably differences and perform the regression on the differences, just like the regression add-in. the fact that the trendline curve is usually pretty good especially if you go to the sixth order polynomial, means that the regression is ok, but the reporting of the coefficients when it translates back to the original dataset is in error. The same results are obtained whether in XP, 2003 or 2007. Microsoft should pay attention! "John1791" wrote: I am trying to work with trendlines, and I can match my data with a polynominal trendline. The plotted trendline matches the data points very closely, but when I apply the formula generated to actual data, my projected values are way off. I believe the problem is that the displayed formula is rounding constants off and not displaying to the number of significant figures required to generate a workable formula. Are there any ways to change any settings to allow Excel to display more figures in the diplayed trendline formulas? Thank you in advance. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
Try this dataset:
X Y 0.76 173 0.77 214 0.78 255 0.79 275.5 0.8 296 0.81 306 0.82 316 0.83 326.5 0.84 337 0.85 345 0.86 353 0.87 361 0.88 369 0.89 374.5 0.9 380 0.91 382.5 0.92 385 0.93 387.5 0.94 390 0.95 392.5 0.96 395 0.97 397 0.98 399 0.99 399 1 399 "Jerry W. Lewis" wrote: Please give an example where the trendline in 2003 gives worse results than LINEST. Prior to 2007, the chart polynomial trendline algorithm was excelent, and such claims usually involved user error such as using a Line chart instead of a Scatter chart, or failure to display enough figures for the chart trendline coefficients. Jerry "BOS" wrote: It looks like there is a problem when the trendline calculates the coefficient for datasets that are way off from the origin. I found the same problem. The fitted curve looks great but the coefficients are way off. The regression algorithm does much better. If the dataset is translated around the means; i.e., use (x-xaverage) and y-yaverage), the trendline predicted values also comes out pretty good. The regression algorithm will presumably differences and perform the regression on the differences, just like the regression add-in. the fact that the trendline curve is usually pretty good especially if you go to the sixth order polynomial, means that the regression is ok, but the reporting of the coefficients when it translates back to the original dataset is in error. The same results are obtained whether in XP, 2003 or 2007. Microsoft should pay attention! "John1791" wrote: I am trying to work with trendlines, and I can match my data with a polynominal trendline. The plotted trendline matches the data points very closely, but when I apply the formula generated to actual data, my projected values are way off. I believe the problem is that the displayed formula is rounding constants off and not displaying to the number of significant figures required to generate a workable formula. Are there any ways to change any settings to allow Excel to display more figures in the diplayed trendline formulas? Thank you in advance. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
I am not clear about your example; what model did you fit to this data, what
results did you get, and why did you think that LINEST gave you more accurate results than the chart trendline? When I fit this data with a polynomial of degree 3 or less in Excel 2003, LINEST and the chart trendline agree sufficiently that it did not seem worth determining which was more accurate. In your earlier note, you mentioned a 6th degree polynomial, which I would hesitate to fit to this data since it has such a narrow range of x -values. Ignoring those misgivings, I found that for a 6th degree polynomial LINEST gave 0 correct figures for every coefficient, while the chart trendline gave approximately 10 correct figures for each of the 7 coefficients. Jerry "BOS" wrote: Try this dataset: X Y 0.76 173 0.77 214 0.78 255 0.79 275.5 0.8 296 0.81 306 0.82 316 0.83 326.5 0.84 337 0.85 345 0.86 353 0.87 361 0.88 369 0.89 374.5 0.9 380 0.91 382.5 0.92 385 0.93 387.5 0.94 390 0.95 392.5 0.96 395 0.97 397 0.98 399 0.99 399 1 399 "Jerry W. Lewis" wrote: Please give an example where the trendline in 2003 gives worse results than LINEST. Prior to 2007, the chart polynomial trendline algorithm was excelent, and such claims usually involved user error such as using a Line chart instead of a Scatter chart, or failure to display enough figures for the chart trendline coefficients. Jerry "BOS" wrote: It looks like there is a problem when the trendline calculates the coefficient for datasets that are way off from the origin. I found the same problem. The fitted curve looks great but the coefficients are way off. The regression algorithm does much better. If the dataset is translated around the means; i.e., use (x-xaverage) and y-yaverage), the trendline predicted values also comes out pretty good. The regression algorithm will presumably differences and perform the regression on the differences, just like the regression add-in. the fact that the trendline curve is usually pretty good especially if you go to the sixth order polynomial, means that the regression is ok, but the reporting of the coefficients when it translates back to the original dataset is in error. The same results are obtained whether in XP, 2003 or 2007. Microsoft should pay attention! "John1791" wrote: I am trying to work with trendlines, and I can match my data with a polynominal trendline. The plotted trendline matches the data points very closely, but when I apply the formula generated to actual data, my projected values are way off. I believe the problem is that the displayed formula is rounding constants off and not displaying to the number of significant figures required to generate a workable formula. Are there any ways to change any settings to allow Excel to display more figures in the diplayed trendline formulas? Thank you in advance. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Trendline Significant Values
While my conclusion from BOS's example is opposite to what BOS concluded
(i.e. I still maintain that the chart polynomial trendline is superior to LINEST in Excel versions prior to 2007), the example itself turns out to be extremely interesting numerically in Excel 2003. I would be very interested in knowing what Excel 2007 does with it. MS has documented a patch http://support.microsoft.com/kb/887964 that seems to fix previous LINEST 2003 errors where coefficients were incorrectly zero but standard errors were not. It appears that this patch also excessively tightens a singularity test, so that different coefficients AND their standard errors are now incorrectly zero. In Excel 2003, LINEST Without the patch calculates correct coefficients for the ill-conditioned 6th degree polynomial with data given at http://groups.google.com/group/micro...9a2bb33e6cdbb8 With the patch, LINEST zeros the linear coefficient and its standard error. If I manually construct an orthogonal basis for the 6th degree polynomial, LINEST without the patch zeros coefficients (but not their standard errors) for powers 3 through 5. With the patch, LINEST correctly handles all coefficients and standard errors from the orthogonal basis, yielding t-tests that agree with those from summary(lm(y~poly(x,6)) in S-PLUS and R. Although the present problem is much less ill-conditioned, LINEST 2003 zeros the cubic coefficient and its standard error with or without the patch. If I manually construct an orthogonal basis for the 6th degree polynomial, LINEST without the patch zeros all coefficients except the intercept and zeros the standard error for the 6th power term. With the patch, LINEST correctly handles all coefficients and standard errors from the orthogonal basis, yielding t-tests that agree with those from summary(lm(y~poly(x,6)) in S-PLUS and R. In summary, LINEST in 2003 is still not to be trusted when it returns coefficients of exactly zero. It remains to be seen whether LINEST in 2007 is any better. Jerry "Jerry W. Lewis" wrote: I am not clear about your example; what model did you fit to this data, what results did you get, and why did you think that LINEST gave you more accurate results than the chart trendline? When I fit this data with a polynomial of degree 3 or less in Excel 2003, LINEST and the chart trendline agree sufficiently that it did not seem worth determining which was more accurate. In your earlier note, you mentioned a 6th degree polynomial, which I would hesitate to fit to this data since it has such a narrow range of x -values. Ignoring those misgivings, I found that for a 6th degree polynomial LINEST gave 0 correct figures for every coefficient, while the chart trendline gave approximately 10 correct figures for each of the 7 coefficients. Jerry "BOS" wrote: Try this dataset: X Y 0.76 173 0.77 214 0.78 255 0.79 275.5 0.8 296 0.81 306 0.82 316 0.83 326.5 0.84 337 0.85 345 0.86 353 0.87 361 0.88 369 0.89 374.5 0.9 380 0.91 382.5 0.92 385 0.93 387.5 0.94 390 0.95 392.5 0.96 395 0.97 397 0.98 399 0.99 399 1 399 "Jerry W. Lewis" wrote: Please give an example where the trendline in 2003 gives worse results than LINEST. Prior to 2007, the chart polynomial trendline algorithm was excelent, and such claims usually involved user error such as using a Line chart instead of a Scatter chart, or failure to display enough figures for the chart trendline coefficients. Jerry "BOS" wrote: It looks like there is a problem when the trendline calculates the coefficient for datasets that are way off from the origin. I found the same problem. The fitted curve looks great but the coefficients are way off. The regression algorithm does much better. .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How would I attach or assign values to a trendline? | Excel Worksheet Functions | |||
how can I sum up the values with more than 15 significant digits? | Excel Discussion (Misc queries) | |||
how do you create a trendline for only the x-axis values in excel | Charts and Charting in Excel | |||
Getting the values of the points in a trendline | Excel Discussion (Misc queries) | |||
How can i find specific values along a trendline? | Charts and Charting in Excel |