trendline coefficient accuracy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
On Thu, 28 Jul 2005 14:41:03 -0700, "Simon Y" <Simon
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.
Click to expand...

I assume you are looking at the generated coefficients from the graph of the
trendline. The display is not, by default, formatted to show you the real
results.

To change this, right click in the area of the equation and select Format Data
Label. Then format Number to 15 places and the resultant numbers should give
you the result you desire.


--ron
 
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/tips/trendline_coefficients.htm

- Mike
www.mikemiddleton.com
 
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/
_______
 
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
 
Back
Top