Excel has some canned trend-line equations, one of which is an exponential fit: y = Ae[sup]–Bx[/sup]. It is sometimes better to fit to an equation that allows for a non-zero asymptote: y = Ae[sup]–Bx[/sup] + C. Is there any way to do this in Excel? I used to use Kaleidagraph, but don’t currently have access to it.
Probably not in Excel proper, but there might be an add-in to do it.
My previous search terms weren’t getting me anywhere, but I found some potential hits for “curve fitting in excel”. In particular, using something called “solver”. Will report back shortly…
Yes, Solver is the way to do it (if you have a version of Excel with Solver – some of the Mac versions, at least, don’t have it). Essentially, you need to set up the spreadsheet with cells holding the values of A, B, and C and a cell calculating the sum of square errors using those values. Then ask Solver to minimize the SSE by modifying A, B, and C.
One thing to be aware of: Most canned exponential regression routines (and I think this applies to Excel’s) don’t minimize SSE. They first convert the exponential into a linear function by taking logs, then use linear regression on that. So, even if you have an example where C should be 0, using Solver as above may give you different A and B than a canned routine would.
Thanks, Topologist. I’ve been playing around with it, essentially simulating an exponential decay with noise and an offset (my “C” from earlier), then using Solver as you described to minimize SSE and return optimized A, B, and C. It’s not perfect, but close enough. I’ll have to look into what the settings do, as the software may be saying “good enough” before I’d like it to.
This whole exercise stems from discussions about chemistry students* measuring 1st-order rate constants. They’re often taught to perform a linear regression on ln(y) vs x. This works fine if their data are pretty. However, if they only monitor the reaction for 3 half lives and have a non-zero asymptote (not that they’d notice if they’re only watching for 3 half lives), they can get some very bogus numbers. I’m hoping to get a feel for how bogus those number might be.
*Often directed to do so by faculty who learned this method before automated fitting of anything non-linear was really an option.
As I mentioned, this is the method most automated fitters will use, too. It’s not my field, but I think there’s an argument for doing this, as it essentially minimizes the percent errors rather than the absolute errors, which may be more appropriate for something that behaves exponentially. Of course, as you say, it doesn’t work if there should be a nonzero asymptote.
There’s nothing “right” about minimizing the sum of squared errors. We do it because it’s derivative is linear so that if we have a underlying linear model in mind, we have a set of simultaneous first order equations which we know how to solve.
That, and if your y values are normal distributed with a mean that’s linear in the x values, the least squares regression coefficients are the maximum likelihood estimate.
Also, the sum of square differences (or its square root) is the standard metric in Euclidean space, so minimizing SSE is minimizing the distance between the vector of predicted values and the vector of observed values. Of course other metrics are possible, but this is the one that generalizes what we usually think of as distance in the plane or in three dimensions.
Countering this Euclidean notion, though, minimizing the SSE in a linear standard regression does not find the line, plane, or hyperplane that is closest to the points by the Euclidean metric.