Trendlines

[Guest]

I used a 6th degree polynomial trendline to smooth a curve on a chart. I
selected the option for printing the trendline formula on the chart so I
could use it in my calculations, but the numbers in the equation are rounded
so that I don't get the right results. Is there any way that I can output
the equation with more significant figures shown?

 

[Jon Peltier]

1. Select the trendline equation, then use the Increase Decimal buttons on
the formatting toolbar to add decimal digits, or press Ctrl+1 (numeral one)
and click on the Number tab, and select a Scientific format with 15 places.

2. A 6th order poly fit is generally not well suited to fitting a curve
unless you are only looking to make the chart look pretty. As Martin Brown
stated in another thread just an hour ago:

You really need to fit a physical model to your data rather than the highest
order polynomial available.

- Jon

 

[Guest]

I don't have the actual equation, only the label on the chart, which is just
text. How can I get the actual equation?

 

[Guest]

Sorry, had one other question. You mentioned needing to fit a physical model
to my data. What does this mean?

 

[Jon Peltier]

What does your data display the behavior of?

If I were studying Hooke's Law, I would plot force on one axis and
displacement on the other, and use the slope as a measure of the rigidity of
the material. The physical model for Hooke's Law is d = k F, where d is
displacement, F is force, and K is stiffness. The physical model for Ohm's
law is V = i R (i.e., voltage = current * resistance).

Knowing a little about the hypothetical basis for the relationship helps me
to decide what kind of curve fit to apply to these models. These are simple
linear models; most physical systems are more complicated, based on
exponential, power law, up to about second order polynomial, and similar
fits. When the fit requires more than second order polynomial fit, the
fitted model is almost guaranteed to be a "looks nice" model, but not
anything that is predictive of the underlying behavior. "Looks nice" fits
are okay to show something in conceptual terms, or if you need to
interpolate within the observed range of data. When you are studying the
underlying relationships, or if you are trying to extrapolate beyond the
observed data (always dangerous, especially with a 6th order poly fit), the
model had better have some physical basis for being selected.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[Jon Peltier]

Bernard Liengme has a nice tutorial on the use of LINEST to compute a poly
fit in a worksheet:

http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Tushar Mehta has a more comprehensive treatment:

http://tushar-mehta.com/publish_train/data_analysis/16.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[Guest]

I'm plotting equipment production for a grading contractor. There are some
constraints to the data that give me an idea of the shape of the curve, but I
think there are too many variables (i.e. weather, material characteristics,
operator skill, etc.) to be able to define the formula on a physical basis.
I will look into it more though. Maybe I am ignoring these constraints too
much. Thanks for your help.

 

[Jon Peltier]

Maybe you just need the "looks nice" approach. It's hard to make predictions
with too many independent variables, especially when some of them are just
noise. You also might want to look at the data in terms of a set of binary
variables (operator A vs B, humid vs dry, material 1 vs material 2, etc) to
see which cause the largest difference in output.

- Jon

 

[Del Cotter]

I'm plotting equipment production for a grading contractor. There are some
constraints to the data that give me an idea of the shape of the curve, but I
think there are too many variables (i.e. weather, material characteristics,
operator skill, etc.) to be able to define the formula on a physical basis.

Even if you only have a time series, there might be cyclic variables in
that which you can include in your model. For instance, is there a
monthly or annual cycle in equipment production? If so, you might be
able to use day of the month or day since the beginning of the year as a
variable. Mike Middleton's book _Data Analysis Using Excel_ has a
discussion of this in Chapter 20, and he has an example work sheet at:

http://www.mikemiddleton.com/LinearTrendSeasonalForecast.xls

It's a bit more advanced than the simple trendlines facility though, I
must admit.