Solver vs. Exponential Trendline

[jcoleman52]

Can anyone (briefly) compare and contrast these two Excel features? We
have a set of data from a study for which we are trying to plot a decay
curve with an accompanying half-life calculation. One option is to
create a plot of the data with an exponential trendline (Y=b*exp^cx).
Another is to use the Solve add-in, utilizing the same equation, and
minimize the sum of the squared deviations by manipulating the
regression coefficients (b and c). Both methods seem to yield a curve
that gives a reasonable approximation of the observed data, but with
slightly different rate coefficients, which will of course yield
slightly different half-lives. Any thoughts on which approach is more
appropriate? Thanks.

 

[FinRazel]

In this case, you should probably choose your curve based on which one most
closely calculates the half-life of a substance having a known (published)
half-life similar to the half-lives that you found experimentally.

 

[B. R.Ramachandran]

Hi,

The Solver result is technically 'more' correct. Because, I guess, you
would have used the exponential equation [y = b*exp(c*x)] with some guess
values of b and c (c should have been negative) to calculate y values,
calculated the SSR [sum of y(experimental) - y (calculated)], and minimized
the SSR by optimizing b and c. This approach uses the raw data 'as is' and,
therefore, the result is more trustworthy.

The exponential trendline, on the other hand, first linearizes the data, ln
y = ln b + c*x, does a LINEAR regression, calculates the slope (c) and
y-intercept (ln b) for the best linear fit, and SHOWS the trendline equation
in the exponential form (which is y = exp(y-intercept) exp(c*x). It does
this by minimizing the SSR of (not the original y data) for the
'transformed', (i.e., ln y) data.

If the experimental data are absolutely free of errors (uncertainties)
[which is never the case], the two results WILL be identical (the minimized
SSR will be zero in both cases). Real-life data, however, contain
uncertainties, and the linear transformation of the data DOES NOT transform
the errors appropriately [UNLESS YOU DO A WEIGHTED REGRESSION].

To verify this, calculate the natural logarithm of y, and fit the ln(y),x
data to the linear equation, ln y = ln b +c*x, using Solver. You would
notice that the b and c values you obtain would pretty much correspond to the
results from the exponential trendline fit.

Regards,
B. R. Ramachandran