Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Ploynomial Trendline Equation Changes After Copy Paste
In Excel 2007 if you create a XY Scatter Plot and then add a polynomial
trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Ploynomial Trendline Equation Changes After Copy Paste
I was unable to reproduce this behaviour. Can you give more details?
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Charles @ Aldenlab" <Charles @ wrote in message ... In Excel 2007 if you create a XY Scatter Plot and then add a polynomial trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Sure....use this data
x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. "Bernard Liengme" wrote: I was unable to reproduce this behaviour. Can you give more details? best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Charles @ Aldenlab" <Charles @ wrote in message ... In Excel 2007 if you create a XY Scatter Plot and then add a polynomial trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
For the data you specified, the cubic coefficient should be 9.6E-09, not
6E-09. IF you are not copying the entire coefficient, that may be why Word is dropping it. If the chart itself is failing to display the correct value, that would be another problem altogether. Note that you can calculate the coefficients yourself, using LINEST. Select three adjacent cells in one row and array enter =LINEST(ydata-100,xdata^{1,2,3},FALSE) That will give you the cubic, quadratic, and linear coefficients for the cubic polynomial with intercept forced to be 100. Jerry "Charles @ Aldenlab" wrote: Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Jerry,
This concerns me even more then. I am not missing any of the function when I copy and paste, we are just literally copying the whole chart, which includes the funciton, and pasting that in any other file, even the same excel document again. I only copied the equation out seperatly to highlight the problem I had noticed, which is that when you copy and paste the entire chart it drops the cubic coefficient for absolutly no reason. Now if what you are pointing out is correct, then Excel is not even computing the Trendline equation correctly in the first place, that is even a bigger problem. Can you verify the errors by following my steps? "Jerry W. Lewis" wrote: For the data you specified, the cubic coefficient should be 9.6E-09, not 6E-09. IF you are not copying the entire coefficient, that may be why Word is dropping it. If the chart itself is failing to display the correct value, that would be another problem altogether. Note that you can calculate the coefficients yourself, using LINEST. Select three adjacent cells in one row and array enter =LINEST(ydata-100,xdata^{1,2,3},FALSE) That will give you the cubic, quadratic, and linear coefficients for the cubic polynomial with intercept forced to be 100. Jerry "Charles @ Aldenlab" wrote: Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
I don't have 2007, but I have verified that both the chart trendline and
LINEST get correct results in 2000 and 2003. In versions prior to 2007, you could right click on the chart equation and reformat to display scientific notation with 14 decimal places; can that still be done in 2007? In older versions that gave y = 9.60309082652360E-09x3 - 2.63556800917837E-05x2 - 2.12916809517409E-02x + 1.00000000000000E+02 which are correct to between 12 and 14 significant figures. Thus far, you have given no evidence of a problem with the calculated linear and quadratic coefficients. If they are right, then the cubic coefficient is probably right internally as well (since the values are inter-related). That would suggest a problem with displaying the equation correctly, as opposed to a problem calculating it correctly. Jerry "Charles @ Aldenlab" wrote: Jerry, This concerns me even more then. I am not missing any of the function when I copy and paste, we are just literally copying the whole chart, which includes the funciton, and pasting that in any other file, even the same excel document again. I only copied the equation out seperatly to highlight the problem I had noticed, which is that when you copy and paste the entire chart it drops the cubic coefficient for absolutly no reason. Now if what you are pointing out is correct, then Excel is not even computing the Trendline equation correctly in the first place, that is even a bigger problem. Can you verify the errors by following my steps? "Jerry W. Lewis" wrote: For the data you specified, the cubic coefficient should be 9.6E-09, not 6E-09. IF you are not copying the entire coefficient, that may be why Word is dropping it. If the chart itself is failing to display the correct value, that would be another problem altogether. Note that you can calculate the coefficients yourself, using LINEST. Select three adjacent cells in one row and array enter =LINEST(ydata-100,xdata^{1,2,3},FALSE) That will give you the cubic, quadratic, and linear coefficients for the cubic polynomial with intercept forced to be 100. Jerry "Charles @ Aldenlab" wrote: Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Jerry,
Ok that explains it. We have been working with this fine in all previous versions of Excel, it is just 2007 that shows this problem. If I reformat to show scientific notation, this is the equation Excel 2007 gives me: y = 5.95241766790664E-09x3 - 2.63556802117476E-05x2 - 2.12916808943767E-02x + 1.00000000000000E+02 If I do the same thing in Excel 2003, I get your result y = 9.60309088524755E-09x3 - 2.63556802117502E-05x2 - 2.12916808943753E-02x + 1.00000000000000E+02 This just denfitly shows there is something screwy in the new version. "Jerry W. Lewis" wrote: I don't have 2007, but I have verified that both the chart trendline and LINEST get correct results in 2000 and 2003. In versions prior to 2007, you could right click on the chart equation and reformat to display scientific notation with 14 decimal places; can that still be done in 2007? In older versions that gave y = 9.60309082652360E-09x3 - 2.63556800917837E-05x2 - 2.12916809517409E-02x + 1.00000000000000E+02 which are correct to between 12 and 14 significant figures. Thus far, you have given no evidence of a problem with the calculated linear and quadratic coefficients. If they are right, then the cubic coefficient is probably right internally as well (since the values are inter-related). That would suggest a problem with displaying the equation correctly, as opposed to a problem calculating it correctly. Jerry "Charles @ Aldenlab" wrote: Jerry, This concerns me even more then. I am not missing any of the function when I copy and paste, we are just literally copying the whole chart, which includes the funciton, and pasting that in any other file, even the same excel document again. I only copied the equation out seperatly to highlight the problem I had noticed, which is that when you copy and paste the entire chart it drops the cubic coefficient for absolutly no reason. Now if what you are pointing out is correct, then Excel is not even computing the Trendline equation correctly in the first place, that is even a bigger problem. Can you verify the errors by following my steps? "Jerry W. Lewis" wrote: For the data you specified, the cubic coefficient should be 9.6E-09, not 6E-09. IF you are not copying the entire coefficient, that may be why Word is dropping it. If the chart itself is failing to display the correct value, that would be another problem altogether. Note that you can calculate the coefficients yourself, using LINEST. Select three adjacent cells in one row and array enter =LINEST(ydata-100,xdata^{1,2,3},FALSE) That will give you the cubic, quadratic, and linear coefficients for the cubic polynomial with intercept forced to be 100. Jerry "Charles @ Aldenlab" wrote: Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Extremely strange! Your cubic coefficient matches to 8-figures the cubic
coefficient for the model with an estimated intercept! I can think of no obvious way for MS to have gotten things crossed up like this. Unfortunately, this is not the only "improvement" in the 2007 chart trendline that has rendered this formerly numerically excellent tool no longer trustworthy. http://groups.google.com/group/micro...353c068ee07b94 I think that LINEST 2007 will still return correct values, using the arguments that I posted earlier. Try generating the trendline from LINEST coefficient estimates and see how it compares to the curve plotted by adding a trendline to the chart (i.e. did MS mess up the displayed equation only, or the plotted curve also?) Jerry "Charles @ Aldenlab" wrote: Jerry, Ok that explains it. We have been working with this fine in all previous versions of Excel, it is just 2007 that shows this problem. If I reformat to show scientific notation, this is the equation Excel 2007 gives me: y = 5.95241766790664E-09x3 - 2.63556802117476E-05x2 - 2.12916808943767E-02x + 1.00000000000000E+02 If I do the same thing in Excel 2003, I get your result y = 9.60309088524755E-09x3 - 2.63556802117502E-05x2 - 2.12916808943753E-02x + 1.00000000000000E+02 This just denfitly shows there is something screwy in the new version. "Jerry W. Lewis" wrote: I don't have 2007, but I have verified that both the chart trendline and LINEST get correct results in 2000 and 2003. In versions prior to 2007, you could right click on the chart equation and reformat to display scientific notation with 14 decimal places; can that still be done in 2007? In older versions that gave y = 9.60309082652360E-09x3 - 2.63556800917837E-05x2 - 2.12916809517409E-02x + 1.00000000000000E+02 which are correct to between 12 and 14 significant figures. Thus far, you have given no evidence of a problem with the calculated linear and quadratic coefficients. If they are right, then the cubic coefficient is probably right internally as well (since the values are inter-related). That would suggest a problem with displaying the equation correctly, as opposed to a problem calculating it correctly. Jerry "Charles @ Aldenlab" wrote: Jerry, This concerns me even more then. I am not missing any of the function when I copy and paste, we are just literally copying the whole chart, which includes the funciton, and pasting that in any other file, even the same excel document again. I only copied the equation out seperatly to highlight the problem I had noticed, which is that when you copy and paste the entire chart it drops the cubic coefficient for absolutly no reason. Now if what you are pointing out is correct, then Excel is not even computing the Trendline equation correctly in the first place, that is even a bigger problem. Can you verify the errors by following my steps? "Jerry W. Lewis" wrote: For the data you specified, the cubic coefficient should be 9.6E-09, not 6E-09. IF you are not copying the entire coefficient, that may be why Word is dropping it. If the chart itself is failing to display the correct value, that would be another problem altogether. Note that you can calculate the coefficients yourself, using LINEST. Select three adjacent cells in one row and array enter =LINEST(ydata-100,xdata^{1,2,3},FALSE) That will give you the cubic, quadratic, and linear coefficients for the cubic polynomial with intercept forced to be 100. Jerry "Charles @ Aldenlab" wrote: Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Jerry,
Thanks so much for helping me out with this. I have made some calls and hopefully raised some alarms at Microsoft about this. This is a MAJOR inconsistency. I ended up taking the equation spit out by Excel 2007 and using that to draw another line on my chart, and guess what, it does not match the line drawn by the trendline function. For fun, I also added the equation generated by Excel 2003, and it matches perfectly. Hopefully nobody at NASA is using Excel 2007 to plot space ship trajectories. I will keep the post updated with any further developments. Chuck "Jerry W. Lewis" wrote: Extremely strange! Your cubic coefficient matches to 8-figures the cubic coefficient for the model with an estimated intercept! I can think of no obvious way for MS to have gotten things crossed up like this. Unfortunately, this is not the only "improvement" in the 2007 chart trendline that has rendered this formerly numerically excellent tool no longer trustworthy. http://groups.google.com/group/micro...353c068ee07b94 I think that LINEST 2007 will still return correct values, using the arguments that I posted earlier. Try generating the trendline from LINEST coefficient estimates and see how it compares to the curve plotted by adding a trendline to the chart (i.e. did MS mess up the displayed equation only, or the plotted curve also?) Jerry "Charles @ Aldenlab" wrote: Jerry, Ok that explains it. We have been working with this fine in all previous versions of Excel, it is just 2007 that shows this problem. If I reformat to show scientific notation, this is the equation Excel 2007 gives me: y = 5.95241766790664E-09x3 - 2.63556802117476E-05x2 - 2.12916808943767E-02x + 1.00000000000000E+02 If I do the same thing in Excel 2003, I get your result y = 9.60309088524755E-09x3 - 2.63556802117502E-05x2 - 2.12916808943753E-02x + 1.00000000000000E+02 This just denfitly shows there is something screwy in the new version. "Jerry W. Lewis" wrote: I don't have 2007, but I have verified that both the chart trendline and LINEST get correct results in 2000 and 2003. In versions prior to 2007, you could right click on the chart equation and reformat to display scientific notation with 14 decimal places; can that still be done in 2007? In older versions that gave y = 9.60309082652360E-09x3 - 2.63556800917837E-05x2 - 2.12916809517409E-02x + 1.00000000000000E+02 which are correct to between 12 and 14 significant figures. Thus far, you have given no evidence of a problem with the calculated linear and quadratic coefficients. If they are right, then the cubic coefficient is probably right internally as well (since the values are inter-related). That would suggest a problem with displaying the equation correctly, as opposed to a problem calculating it correctly. Jerry "Charles @ Aldenlab" wrote: Jerry, This concerns me even more then. I am not missing any of the function when I copy and paste, we are just literally copying the whole chart, which includes the funciton, and pasting that in any other file, even the same excel document again. I only copied the equation out seperatly to highlight the problem I had noticed, which is that when you copy and paste the entire chart it drops the cubic coefficient for absolutly no reason. Now if what you are pointing out is correct, then Excel is not even computing the Trendline equation correctly in the first place, that is even a bigger problem. Can you verify the errors by following my steps? "Jerry W. Lewis" wrote: For the data you specified, the cubic coefficient should be 9.6E-09, not 6E-09. IF you are not copying the entire coefficient, that may be why Word is dropping it. If the chart itself is failing to display the correct value, that would be another problem altogether. Note that you can calculate the coefficients yourself, using LINEST. Select three adjacent cells in one row and array enter =LINEST(ydata-100,xdata^{1,2,3},FALSE) That will give you the cubic, quadratic, and linear coefficients for the cubic polynomial with intercept forced to be 100. Jerry "Charles @ Aldenlab" wrote: Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Yes, I see what you mean.
BTW: the problem also occurs when you copy to the SAME Excel file I get the same coefficients with LINEST as reported by Jerry Sorry to reply so late -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Charles @ Aldenlab" wrote in message ... Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. "Bernard Liengme" wrote: I was unable to reproduce this behaviour. Can you give more details? best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Charles @ Aldenlab" <Charles @ wrote in message ... In Excel 2007 if you create a XY Scatter Plot and then add a polynomial trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
Here is another thread reporting yet another new bug with the chart trendline
http://groups.google.com/group/micro...752db0fedc2cbf With all the statistical issues that have needed attention for over 15 years, one wonders why MS started by "fixing" something that wasn't broken. Jerry "Jerry W. Lewis" wrote: Extremely strange! Your cubic coefficient matches to 8-figures the cubic coefficient for the model with an estimated intercept! I can think of no obvious way for MS to have gotten things crossed up like this. Unfortunately, this is not the only "improvement" in the 2007 chart trendline that has rendered this formerly numerically excellent tool no longer trustworthy. http://groups.google.com/group/micro...353c068ee07b94 I think that LINEST 2007 will still return correct values, using the arguments that I posted earlier. Try generating the trendline from LINEST coefficient estimates and see how it compares to the curve plotted by adding a trendline to the chart (i.e. did MS mess up the displayed equation only, or the plotted curve also?) Jerry |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Polynomial Trendline Equation Changes After Copy Paste
No problem...I am just glad you where able to duplicate the problem. I also
found out the problem occurs also if you just save the Excel file and reopen it....poof, missing coefficients. "Bernard Liengme" wrote: Yes, I see what you mean. BTW: the problem also occurs when you copy to the SAME Excel file I get the same coefficients with LINEST as reported by Jerry Sorry to reply so late -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Charles @ Aldenlab" wrote in message ... Sure....use this data x y 1252.531345 44.0462161 1399.141231 54.03976206 1400.056416 44.5415568 1398.717824 36.45095719 1121.321671 69.02084478 1120.421493 57.35527023 1121.880135 58.10791134 1015.828469 52.80347407 842.7187937 73.74794476 755.8568419 69.50308062 841.0183109 68.54248481 560.0202985 81.17169689 503.6105019 79.47102425 560.4900412 81.38340795 281.0801761 94.02451592 252.2988083 96.7705903 281.3767037 92.81489618 0 100 Plot that data on an XY Scatter Plot in Excel 2007. Select the series, then right click and "Add Trendline" Use the following properties for the trendline: Trend/Regression Type: Polynomial - Order 3 Trendline Name: Automatic Check Set Intercept = 100 Check Display Equation on chart Check Display R-squared value on chart You will get an equation: y = 6E-09x3 - 3E-05x2 - 0.021x + 100 Now, copy the chart and paste it into another Excel 2007 document, Word 2007, Powerpoint 2007, ect. document and note the equation. It will change to the following: y = - 3E-05x2 - 0.021x + 100 This is the previous equation minus the x^3 term. If you check the Trendline properties, they are all still set as they where in the original chart. The only way to get the equation working correctly again is to remove then readd the trendline. I hope you can reproduce this error, this occures on all of our Office 2007 installations here, Pro Plus and Standard about 40 copies. If there are others in the engineering world experiencing this problem, and not noticing it, there could be serious concequences for the projects they are working on. "Bernard Liengme" wrote: I was unable to reproduce this behaviour. Can you give more details? best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Charles @ Aldenlab" <Charles @ wrote in message ... In Excel 2007 if you create a XY Scatter Plot and then add a polynomial trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trendline equation | Charts and Charting in Excel | |||
Trendline Equation | Excel Worksheet Functions | |||
trendline equation extract | Charts and Charting in Excel | |||
Rounding in Trendline Equation | Charts and Charting in Excel | |||
Logarithmic Trendline Equation | Charts and Charting in Excel |