Polynomial trendline formula doesn't match values on curve
- Thread starter Graham
- Start date
Graham
Firstly welcome to the Chandoo.org Forums
Right Click on the Charts Trendline Formula Box
Change the Number Format to #,###.000000000000000
Try your equation again
Within Excel the equations are treated as Double Precision numbers, but by rounding the numbers in the equation box, they are truncated and missing obviously important data.
Refer: Limitations section in http://chandoo.org/wp/2011/01/27/trendlines-and-forecasting-in-excel-part-3/
Firstly welcome to the Chandoo.org Forums
Right Click on the Charts Trendline Formula Box
Change the Number Format to #,###.000000000000000
Try your equation again
Within Excel the equations are treated as Double Precision numbers, but by rounding the numbers in the equation box, they are truncated and missing obviously important data.
Refer: Limitations section in http://chandoo.org/wp/2011/01/27/trendlines-and-forecasting-in-excel-part-3/
Dear Hui, thanks for the prompt response but I don't seem to have got there yet.
I right clicked on the textbox containing the equation and selected "Format Trendline Label". I then selected Number in the lefthand field, then Number in the Category field and selected 15 decimal places. The numbers in each term of the equation all changed but were mainly zeros until about the 11th or 12th decimal place so the it didn't materially change the 'Y' values which remain very different from the values if read off the trendline.
Any further thoughts?
I right clicked on the textbox containing the equation and selected "Format Trendline Label". I then selected Number in the lefthand field, then Number in the Category field and selected 15 decimal places. The numbers in each term of the equation all changed but were mainly zeros until about the 11th or 12th decimal place so the it didn't materially change the 'Y' values which remain very different from the values if read off the trendline.
Any further thoughts?
Dear Hui,
Since my last post I have created a new worksheet and recreated the 2 charts and then added trendlines and equations. Amazingly, at least to me, while the 3 points for each chart were unchanged, the equations changed radically. In the first case y = 14.46x^2 - 11.62x + 8,378.16 changed to y = 0.58x^2 - 119.14x + 14,511.00. I then used the new equation with the 3 original x values and all got exactly the correct y values. I tried another 4 values of x and all gave y values that appear to match the trendline. All this with a setting of 2 decimal places.
In the case of the second chart the equation had changed from y = 0.68x^2 + 2.05x + 99.75 to y = 0.00x^2 - 0.14x + 10.40. When I tested the formula (with 2 decimal places) the values didn't match exactly the 3 original values of y. Nor did the other 4 points exactly match the trendline. So for the second chart I increased the equation to 6 decimal places. The equation became y = 0.000640x^2 - 0.144000x + 10.400000 and now all 3 original and 4 extra values of x gave very good agreement, as was the case with the first chart.
So in summary, I am at a loss to understand why the equations in the first worksheet were wrong, yet in a new worksheet using the same table of data the equations were okay. At the same time I can see how increasing the number of decimal places at least in some cases gives better results.
Thanks for your assistance. I have learnt things I didn't know previously.
Regards, Graham
Since my last post I have created a new worksheet and recreated the 2 charts and then added trendlines and equations. Amazingly, at least to me, while the 3 points for each chart were unchanged, the equations changed radically. In the first case y = 14.46x^2 - 11.62x + 8,378.16 changed to y = 0.58x^2 - 119.14x + 14,511.00. I then used the new equation with the 3 original x values and all got exactly the correct y values. I tried another 4 values of x and all gave y values that appear to match the trendline. All this with a setting of 2 decimal places.
In the case of the second chart the equation had changed from y = 0.68x^2 + 2.05x + 99.75 to y = 0.00x^2 - 0.14x + 10.40. When I tested the formula (with 2 decimal places) the values didn't match exactly the 3 original values of y. Nor did the other 4 points exactly match the trendline. So for the second chart I increased the equation to 6 decimal places. The equation became y = 0.000640x^2 - 0.144000x + 10.400000 and now all 3 original and 4 extra values of x gave very good agreement, as was the case with the first chart.
So in summary, I am at a loss to understand why the equations in the first worksheet were wrong, yet in a new worksheet using the same table of data the equations were okay. At the same time I can see how increasing the number of decimal places at least in some cases gives better results.
Thanks for your assistance. I have learnt things I didn't know previously.
Regards, Graham