×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

 

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

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

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

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

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

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

(OP)
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.

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

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(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))

There was some discussion about syntax of linest here:
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))

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

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

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/
 

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

eBook - The Future of Product Development is Here
Looking to make the design and manufacturing of your products more agile? For engineering and manufacturing organizations, the need for digital transformation of product development processes just became more urgent than ever so we wanted to share an eBook that will help you build a practical roadmap for your journey. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close