Excel curve fitting, using linest to fit y=a(1-exp(-bx)) 2

[malford]

thread770-184726

Can anyone help me to use the excel linest() function to fit the curve

y=a(1-exp(-bx))

to my dataset? I'm sure it is possible but I haven't managed to understand the logic to how the functions are derived.

Many thanks for any help!

some sample data if it helps:
X Y
0.0000 0
0.0400 0.660049499
0.0800 1.266123728
0.1200 1.822636491
0.1600 2.333640657
0.2000 2.802857672
0.2400 3.233704663
0.2800 3.629319322
0.3200 3.992582758
0.3600 4.32614048
0.4000 4.632421659

where a=8.0716 b=2.1328

 

[electricpete]

Attached, the problem was solved using "solver" to adjust coefficients a, b to minimize sum of squares of errors between actual y and the y calculated from those coefficients

There is a screen-shot of the solver dialogue box with parameters entered just before hitting "solve".

It requires the analysis tookpak. Tools / Add-ins .... check "analysis toolpak".

=====================================
(2B)+(2B)' ?

 

[electricpete]

Sorry, the name of the add-in is "solver".
Analysis tool-pak is different.

=====================================
(2B)+(2B)' ?

 

[malford]

Thanks electricpete, a good solution. Unfortunately I was really looking to use the linest() function becuase it calculates directly without user interaction. In my spreadsheet the data set is imported by VBA code and pruned for data that is far from the best fit line through several iterations to provide calibration data. I have used linest() to perform a quadratic curve fit but the exponential curve is better.

 

[electricpete]

A few comments/thoughts:

1 – Solver can be manipulated from vba. How to do it: may suffice to record it and inspect the code. Also there have been some threads on that.

2 – Linest provides best fit of a column of dependent variable data to several columns of “independent variable data”. That lends itself well if each of the columns of “independent variable” data can be calculated from x and the resulting fit function is a sum of those terms multipled by unknown constantst to be solved for. Unfortunately, the function a(1-exp(-bx)) where a and b are unknowns to be solved cannot be formulated as a sum of unknown coefficients multipled by terms dependent on x. (How would you construct a column exp(-b*x) when you don’t know b!). Nor can ln = ln(a) + ln(1-exp(-b*x). However you can certainly fit a higher order polynomial like y = C0 + C1*X+C2*X^2+C3*X^3 + C4*X^4+....C10*X^10

=====================================
(2B)+(2B)' ?

 

[electricpete]

There was some discussion about syntax of linest here:
thread770-214997

One thing to note is you can use array formula's to avoid creating extra columns.

Attached uses this method to fit 8th order polynomial to your data (data corresponds to y=a(1-exp(-bx))). The 8th order polynomial matches quite well as shown in the graph.

=====================================
(2B)+(2B)' ?

 

[electricpete]

One downside of the polynomial approach is that the prediction may blow up if you try to extrapolate it far outside of the range of the data which was originally used to estimate the coefficients.




=====================================
(2B)+(2B)' ?

 

[IDS]

You should be cautious about using the Excel Linest function with polynomials higher than fourth order. See:

http://newtonexcelbach.wordpress.com/2011/02/04/fitting-high-order-polynomials/

Conclusions were:

- The Excel Linest function and polynomial chart trendline produce different results for 6th order polynomials in the cases examined. As noted by Lori Miller in the comments to the previous Linest post, this is probably because of changes made to the algorithm for dealing with co-linear data.

- The matrix function (at least in this case) did not give good results beyond fourth order.

- For most interpolation purposes use of a cubic spline will normally give better results than a high order polynomial.

- For cases where a high order polynomial is appropriate the ALGLIB PolynomialFit routine appeared to give much better results than Linest. In separate tests PolynomialFit was found to be stable up to at least 50th order, with the data presented here.

For anyone interested in the Alglib routine, there is an Excel version available here:

http://newtonexcelbach.wordpress.com/2011/02/17/alglib-linear-and-polynomial-fitting-functions/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[malford]

Electricpete - many thanks for your clear explanation of why linest can't be used. I understand a lot better now! I will have a look at using solver from VBA as you suggest. I am particularly interested in using the exponential curve rather than a polynomial because it gives an excellent fit to the data and minimises the data that must be given to the end user of the equipment being calibrated, a and b values will be given on a barcode with other calibration data.
IDS - thanks for your comments also, this looks very interesting for future reference.

 

[IDS]

malford - you might also like to take a look at:

http://newtonexcelbach.wordpress.com/2011/03/01/non-linear-regression-1/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/