can't reproduce trendline with original x values

[whburling]

i am having a difficult time replicating Excel 365 trendlines for the original data set x values.

manually inputting the data set into the trendline equation that excel gave me does not
reproduce the curve that excel drew for me when i asked for a trendline.

However, inputting the data set with the x values of integers from 1 to 20 reproduces the
at least the shape of the curve that excel 365 gave me.

i am graphing the trendline with a graph type of "scatter". I tried two forms of fit.
polynomial to the second degree and logarithmic. neither can be reproduced with the original
x values.

here is the data set that i can not reproduce
x values y values
2005 0.00089
2006 0.00364
2007 0.00420
2008 0.00385
2009 0.00430
2010 0.00474
2011 0.00495
2012 0.0049shape
2013 0.00545
2014 0.00556
2015 0.00533
2016 0.00588
2017 0.00619


here is the data set that produces at least the excel curve shape
x values y values
1 0.00089
2 0.00364
3 0.00420
4 0.00385
5 0.00430
6 0.00474
7 0.00495
8 0.00497
9 0.00545
10 0.00556
11 0.00533
12 0.00588
13 0.00619

when i fit the data set with a logarithmic tendline, the equation excel
365 gave me was: y = 0.0017ln(x) + 0.0017

when i fit the data set with a polynomial trendline, the equation excel
365 gave me was: y = -3.0E-05x^2 + 0.0007x + 0.0014


Please help me understand what is going on. thank you

[MrShorty]

It is difficult to debug this sort of thing without the actual spreadsheet to look at.

If I run the regression on the first data set, I get something vastly different from the reported coefficients. I get
y=0.61*ln(x)-4.63
y=-3.0E-5x^2+0.12x-121.06

If I run the regression on the second set, I get the same coefficients that you reported.

I interpret the results that, for some reason, Excel is treating the x data for your trendline regression as count numbers (1,2,3,...) rather than years. Often this is due to text somewhere in the x values range, or using something other than a scatter chart. Without seeing your file, it is difficult for us to explore what is causing Excel to ignore your x values when performing the regression.

[whburling]

Thank you so much for your response.

Due to your review, I went back to my original chart and checked chart type. It was NOT a scatter chart type as all my other charts were. I am so sorry i wasted your time. I have to be more careful.

Thank you very much for the time you invested in me.

respectfully,
bil

[MrShorty]

That would do it.

One thing I will suggest. I (almost) never use the chart trendlines for my regressions. I prefer to use the LINEST() function directly in the spreadsheet and bypass the chart altogether. Note that all of the available trendlines in Excel's charts (except for the moving average) are "linear" functions and can be regressed using linear regression techniques. For example, you could use =LINEST(B2:B14,LN(A2:A14)) to get the logarithmic trendline. Then you won't have to worry about accidentally picking the wrong chart type. You also won't need to worry about copying coefficients from the chart to the spreadsheet. https://support.office.com/en-us/art...a-fa7abf772b6d

[whburling]

great advice.

Thank you, very very much
bil