Trendline fits badly

I looked around a little bit, but I couldn't find any math-heavy documentation for how trendlines are calculated by excel; if the methodology is published, it's buried under "baby's first trendline"-type blog posts etc.

Offhand? The number that the linefit used by default may have been too imprecise to capture the data behavior. Or maybe you had better starting values for your 2nd approximation compared to your first; or maybe you told Solver to use more steps than the trendline uses by default. That's all just semi-wild guessing by me, though.

I am not enough of a statistician to give you the full analysis, but a few notes and observations to prompt your own research and study:

1) The chart trendlines and the LINEST/LOGEST functions use the same basic linear least squares algorithm. I am not going to remember all of the details correctly off the top of my head, but linear least squares regression comes down to solving a matrix equation like Y=A*X (where Y, A, and X are matrices). The key step in solving for the coefficient matrix is, of course, the matrix inversion algorithm (computer scientist generally argue that QR decomposition is the most efficient, most numerically stable algorithm, and so Excel uses a QR decomposition algorithm).

2) One key thing to realize when working with exponential equations is recognizing the transformation used to make the function "linear". In the case of y=b*exp(mx), the actual equation that is fed into the linear least squares algorithm is ln(y)=ln(b)+mx. This means that the objective function is really minimize (ln(ymeas)-ln(ycalc))^2. Your analysis of the equation is based on the standard (ymeas-ycalc)^2 objective function -- not the same objective function.

3) This latter point is an important part of this kind of analysis, and only you as the analyst can decide which is more correct. One of the criticisms of the standard least squares objective function (ymeas-ycalc)^2 is that it tends to strongly favor larger values over smaller values. Your values range from 10ish to 1000ish -- 3 orders of magnitude. I think if you analyzed your results more closely, you will see that your equation shows a strong preference for fitting the larger y values and almost ignores the small y values. One could argue that the chart trendline does not have the same bias, though, as you describe it, it might be biased towards fitting the small values. I think this will be easier to see if you plot your data on a semilog plot (format y axis to be logarithmic).

That should be a brief explanation of why you are getting two different regressions. It will be up to you to decide which variation is more correct.

R2 = 0.997 if you use a power series trendline, which is linear on a log-log chart.

Row\Col	A	B	C	D	E
1		2.4401	-9.9740	B1:C3: {=LINEST(LN(B8:B17), LN(A8:A17), , TRUE)}
2		0.0448	0.2755
3		0.9973	0.0984
4
5			4.660E-5	C5: =EXP(C1)
6
7	x	y	Fit
8	100	3.57	3.54	C8: =$C$5 * A8^$B$1
9	200	17.78	19.19
10	300	63.61	51.61
11	400	89.52	104.13
12	500	169.86	179.49
13	600	292.38	280.07
14	700	410.86	407.96
15	800	568.38	565.09
16	900	756.76	753.25
17	1000	977.62	974.07