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

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

(OP)

thread770-184726: Excel Curve Fit Coefficients

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

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

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

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)' ?

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

Analysis tool-pak is different.

=====================================

(2B)+(2B)' ?

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

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

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(y) = 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)' ?

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

thread770-214997: Multivariable regression in Excell

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)' ?

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

=====================================

(2B)+(2B)' ?

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

http://ne

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:

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

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

IDS - thanks for your comments also, this looks very interesting for future reference.

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

ht

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/