Newbie trouble with polynomial trendline equation

  • Thread starter Thread starter Medusa
  • Start date Start date
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?
 
Medusa -
What am i doing wrong? <
Click to expand...

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? <
Click to expand...

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

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

- Mike

http://www.MikeMiddleton.com
 
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
from the menu
 
Back
Top