Home |
Search |
Today's Posts |
#1
|
|||
|
|||
trendline coefficient accuracy
I am trying to establish the polynomial coefffiecients for a fuel
consumtption curve for one of our ships. I have plotted the recorded consumption data from the ship and trended them with a 3rd power polynomial. the curve folllows the data well, however, the set of genertated coefficients, which when put back into the graph to generte test curve that in no way refelct the original tend line. and can't be used to forcatst the fuel budget. I must be doing something stupid. Thanks, Simon. |
#2
|
|||
|
|||
|
#3
|
|||
|
|||
Simon -
Several comments: (1) A 3rd order polynomial may be overfitting your data. It may fit the original data well, but it may not be useful for predicting future values. (2) If you're using a Line chart type instead of an XY (Scatter) chart type, Excel is using the values 1,2,3,... for X. (3) If you're using an XY (Scatter) chart type and if any of the X data is text instead of numerical, Excel may be using 1,2,3,... for X. (4) Select the trendline equation and repeatedly click the Increase Decimal button to display more significant digits. (5) To obtain the coefficients in worksheet cells, see the "Regression with polynomials" section at http://www.tushar-mehta.com/excel/ti...efficients.htm - Mike www.mikemiddleton.com "Simon Y" <Simon wrote in message ... I am trying to establish the polynomial coefffiecients for a fuel consumtption curve for one of our ships. I have plotted the recorded consumption data from the ship and trended them with a 3rd power polynomial. the curve folllows the data well, however, the set of genertated coefficients, which when put back into the graph to generte test curve that in no way refelct the original tend line. and can't be used to forcatst the fuel budget. I must be doing something stupid. Thanks, Simon. |
#4
|
|||
|
|||
Simon -
You're probably seeing errors due to truncation of the formula's coefficients. Select the trendline formula in the chart, select Selected Data Labels from the Format menu, and on the Number tab, pick a format with lots of significant digits. A scientific format with 15 decimal digits is about the most you'll get. You could also use LINEST to calculate the formulas directly in the worksheet, or search Google Groups for a post by Dave Braden which has a VBA solution to extract trendline coefficients. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Simon Y wrote: I am trying to establish the polynomial coefffiecients for a fuel consumtption curve for one of our ships. I have plotted the recorded consumption data from the ship and trended them with a 3rd power polynomial. the curve folllows the data well, however, the set of genertated coefficients, which when put back into the graph to generte test curve that in no way refelct the original tend line. and can't be used to forcatst the fuel budget. I must be doing something stupid. Thanks, Simon. |
#5
|
|||
|
|||
I have very little to add to Mike Middleton's comprehensive reply,
except to note that - for Mike's comment (4), the simplest approach is to format in scientific notation with 14 decimal places. - If the chart is an "XY (Scatter)" chart with exclusively numeric X data (cf. Mike's comments 2 & 3) and the coefficients still differ significantly from LINEST, then the fitting problem is ill-conditioned and the chart coefficients are more accurate. Jerry Mike Middleton wrote: Simon - Several comments: (1) A 3rd order polynomial may be overfitting your data. It may fit the original data well, but it may not be useful for predicting future values. (2) If you're using a Line chart type instead of an XY (Scatter) chart type, Excel is using the values 1,2,3,... for X. (3) If you're using an XY (Scatter) chart type and if any of the X data is text instead of numerical, Excel may be using 1,2,3,... for X. (4) Select the trendline equation and repeatedly click the Increase Decimal button to display more significant digits. (5) To obtain the coefficients in worksheet cells, see the "Regression with polynomials" section at http://www.tushar-mehta.com/excel/ti...efficients.htm - Mike www.mikemiddleton.com "Simon Y" <Simon wrote in message ... I am trying to establish the polynomial coefffiecients for a fuel consumtption curve for one of our ships. I have plotted the recorded consumption data from the ship and trended them with a 3rd power polynomial. the curve folllows the data well, however, the set of genertated coefficients, which when put back into the graph to generte test curve that in no way refelct the original tend line. and can't be used to forcatst the fuel budget. I must be doing something stupid. Thanks, Simon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trendline Extract | Charts and Charting in Excel | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Trendline to ignore empty cells | Charts and Charting in Excel | |||
How do I write a trendline constant into a cell? | Charts and Charting in Excel | |||
How do I get the trendline equation from Excel to script? | Charts and Charting in Excel |