Curve fitting question
x / y
1 / 2.66
2 / 3.23
3 / 3.70
4 / 4.02
5 / 4.24
The 5 points above are from a process that is known to follow the relation
y = a - b*exp(-cx)
Can anyone find a, b and c?
Or does anyone have an effective method for doing so?
Right, best fit. Sorry for not making that clear. Whether it be least squares or something else, I'll leave that open.
Scythian's answer is close to the source function. But I believe that's an exact fit for 3 points. Suppose there were a lot more than 5 data points...
Bekki: I didn't excel had that feature. It's amazing that excel can solve that kind of problem. The only thing is that we don't know their algorithm.
Jon: exp(+0.11*x) curves in the opposite direction to the data points. I'm sure a power law fit works well for the points at hand, but one major difference is that a power law fit does not have an asymptote. These points do have an unknown asymptote.
- JonDihonLv 61 decade agoFavorite Answer
Rewrite the equation to y-a = -b*exp(-cx).
Then ln both sides.
ln(y-a) = ln(-b) -cx.
Use excel to find the best fit regression line for different values of "a".
For a = 0 the correlation coefficient is 0.970825; a pretty good fit.
But the correlation coefficient gets closer and closer to 1 as "a" approaches -infinity.
I'd stick with a = 0. Then c = -0.115127 and b = -2.49332.
y = 2.49332*exp(0.115127*x)
"a" correlation coefficient
That's the best fit regression line for ln(y-a) vs. x.
The points fit this relation better y = ax^n.
To find "a" and "n" ln both sides of the equation.
ln(y) = ln(a) + n*ln(x).
Find "a" and "n" in excel as above.
The correlation coefficient is > 0.999 and does not improve by adding a constant as in the previous model.
y = 2.655703*x^0.294818
Ah so! It is. I'll take another look at it tomorrow. I have to go now.
I was depending on the regression formula to assign the correct signs, but the log prevented that.
I redid the calculations forcing "-b" to be negative and got these values for a, b and c: (4.8307, 3.038585704, 0.328329164). These should be rounded off.
The correlation coefficient is -0.999814024.
The calculated points are (1, 2.64253561), (2, 3.25494606), (3, 3.695958711), (4, 4.013543362), (5, 4.242244307).
I still have some doubts about this especially when I consider first and second derivatives. I might post some further calculations later.
Congratulations on expanding your avatar. The shark is still missing.
Analysis of the gradients:
Consider the gradients at the points of tangency where dy/dx = Δy/Δx.
x, y, Δy/Δx, x_t
2 3.23 0.57 1.48651
3 3.70 0.47 2.48651
4 4.02 0.32 3.48651
5 4.24 0.22 4.48651
The tangent point where dy/dx = Δy/Δx:
y = a - b*exp(-cx)
dy/dx = bc*exp(-cx)
Now find the regression line for ln(dy/dx) = ln(bc*exp(-cx))
ln(dy/dx) = ln(bc)-cx
The gradient is -c. The intercept is ln(bc).
Solve for b and c.
Insert those into the original equation; y = a - b*exp(-cx).
Find a for each x; [1, 5].
Average these values of a.
This gives a = 4.831800643, b = 3.010062207, c = 0.324043814.
These values agree very well with the values found by the previous method.
- Anonymous1 decade ago
Let's do this numerically. You can use excel, since you seem to be proficient at it. Unfortunately, I don't have excel on this computer handy, so I can't tell you the answer, but I can walk you through it.
The first thing you need is a metric. The most common is least squares. So do the following.
Make a column for x and enter your data
Make a column for y and enter your data
Make 3 boxes for a, b, and c
Make a column for f(y). Type in your formula in the top box of the column. a - b * exp(-c*x). a, b, and c, reference your three boxes and x references the first data point in the x column. Then fill down, so your formula gets copied down the column. Don't forget to put dollar signs on the constants, so when you fill down, the x changes, but the constants do not.
Now make a column for ( f(y) - y)^2. In the top box, take the difference between the first f(y) and the first y, and square it. Then fill down.
Sum up the squared errors.
Now comes the tricky part. Use excel's minimizer. You can tell it to minimize the sum of the squared errors by adjusting a, b, and c. That will give you your coefficients.
To get the minimizer tool, you have to add a plugin. It's available in standard excel, but not loaded at first if I recall correctly. It takes like 10 seconds to do, but since I don't have it in front of me, I can't remember the exact steps.
You have to enable "Solver". It's on the tools menu I think. You load it and then go to tools/solver and it brings up a box. You pick a box which you want to maximize/minimize/find the root. You pick the boxes you want to adjust. You can also put in additional constraints (not necessary here).
Once you've done it a couple times, it's really easy and simple, but the first time is tough unless someone shows you how to do it--I had to figure it out on my own and it took me a day to research just to find how to get the tool active.
- Scythian1950Lv 71 decade ago
You have 3 unknowns for 5 equations. After a bit of fiddling around, I see that I can only get 3 of them right, leaving the other 2 only in the rough approximation. For example, a = 4.823, b = 3.002, c = 0.328 will get it right for x = 1, 3, 5, but not for x = 2, 4. I have my doubts if there's any a, b, c such that all 5 would be dead on.
I think a = 4.703, b = 3.177, c = 0.384 gives a reasonably good fit for the given 5 points.
- Anonymous1 decade ago
You're just trying to do a "best fit" here for the given data points, not an exact, right? It's been a while since I took non-linear programming, but I'd think an algorithm like Gauss-Newton would help find the a, b, and c for a best fit: