# Newbie trouble with polynomial trendline equation

M

#### Medusa

This is the polynomial trendline equation I generated from a series of data
items. I've been trying for hours to write the equation properly in my
cells, but I do not seem to be doing it right. I'm a newbie at excel.

y = -3E-08x6 + 1E-06x5 - 4E-05x4 + 0.0005x3 + 3E-05x2 + 0.0101x - 0.0048
R2 = 1

Where x = percentage of job duration (B171/\$D\$165) (ex: .05)
and y = percentage of allotted hours used (ex: .006)

MY EQUATION:

=(-0.00000003*((B171/\$D\$165)^6))+(0.000001*((B171/\$D\$165)^5))-(0.00004*((B171/\$D\$165)^4))+(0.0005*((B171/\$D\$165)^3))+(0.00003*((B171/\$D\$165)^2))+(0.0101*(B171/\$D\$165))-0.0048

What am i doing wrong?... I'm getting negative numbers after plugging the
equation into my spreadsheet, so obviously I am re-writing the equation
wrong... Thoughts?

M

#### Mike Middleton

Medusa -
What am i doing wrong? <

You could use Excel's 15 significant digits to obtain better results (by
selecting the trendline equation and clicking the Increase Decimal key
repeatedly).
Thoughts? <

Use Google to search for "excel polynomial regression overfit" (without the
quotes).

After reading some of those items, if you still want to use a sixth-degree
polynomial, use worksheet functions, described by John Walkenbach at

- Mike

http://www.MikeMiddleton.com

T

#### Tushar Mehta

If you have a reason to believe that a 6th order polynomial is the way
to go, fine but that is rarely the case.

For options on how to get coefficients into a worksheet see
Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/publish_train/data_analysis/16.htm
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select