Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Newbie trouble with polynomial trendline equation
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Newbie trouble with polynomial trendline equation
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 http://spreadsheetpage.com/index.php...line_formulas/ - Mike http://www.MikeMiddleton.com "Medusa" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Newbie trouble with polynomial trendline equation
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_...nalysis/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 "Medusa" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Polynomial trendline XL2007 | Excel Discussion (Misc queries) | |||
Polynomial trendline XL2007 | Charts and Charting in Excel | |||
using polynomial trendline equations | Charts and Charting in Excel | |||
polynomial trendline | Excel Worksheet Functions | |||
Polynomial trendline | Excel Worksheet Functions |