Fit Nonlinear Model to Data with Excel

Microsoft Excel adjusts parameters to minimize the difference between measured and predicted values. This tutorial demonstrates how to calculate the sum of squared errors and minimize error with the Solver add-in. See apmonitor.com/che263/uploads/M... for the raw data used in this example.

[@mjmeternal2696]

I learnt a lot especially that point where you imported data.

[@alvafla]

Very useful video, I did it with a "complex" curve and fitted it very well (impossible with polynomial equation)... thank you

[@suryavamsi9201]

Thank you so much, well explained

[@CodyVLopez]

Wow this is awesome, thank you for making this! Didn't know that solver could make it so easy

[@apm]

It is a great tool for small to medium sized problems in optimization.

[@anisizyanilee5035]

Thank you so much! Very helpful.. God bless you :)

[@anasforum7101]

Thank you very much, it's a fantastic tutorial, I love it.

[@miguelangelsanchez5959]

7:34 Very good explanation, thank you.

[@mohammadalikeshtiara3573]

Thank you so much. really helpful

[@DigitalicaEG]

Very helpful thank you

[@KamalSingh-dn7gv]

Excellent video. Thank you.

[@eyberantezana8843]

Excelente, gracias!

[@SH-pc4xt]

Thanks very much for posting this helpful into tutorial to using Solver in Excel. One thing that seemed odd was referring to the equation you're fitting as 'the correlation'. It's not a correlation, it's an equation with parameters. When these are suitably adjusted the result may (or may not) produce a calculated result that matches the original data.

[@apm]

Thanks for that clarification. Exact wording is important to distinguish between a statistical definition of "the process of establishing a relationship or connection between two or more measures" and a more common definition of "a mutual relationship or connection between two or more things". It is common in some disciplines to refer to equations with parameters adjusted to minimize a measure of closeness as correlations. I like your definition because it is more specific and doesn't create any ambiguity across disciplines.

[@gabrielrt]

Thank you so much!

[@9986916092]

THANK YOU VERY MUCH

[@ThinhTran-yu9sn]

Thank you very much

[@areejiman1304]

Thank you so much

[@NalinJayaratne]

Well explained.

[@apm]

Thanks for the encouraging comment, Nalin.

[@mahmoud_ali_963]

thank you man you are the best :D

[@niguvoice2511]

If I want to do fittings on a 2Y graph on excel. What should I do?

[@apm]

For multiple regression try Python: apmonitor.com/pds/index.php/Main/RegressionOverview

[@blrd3277]

Great video. Need more info for excel after this step is done.
Could you suggest how to estimate quality of the fit by not only adding Pearson's coefficient, but also standard errors for each variables, c1, c2, c3, c4. This essentialy equalize Excel to other fitting software like Kaleidagraph and Origin. If we can draw various confidence bounds in Excel and have also "third type of axis" -Normal (Excel only ahve linear and log axis)- then we can approach possibiliies of Minitab. Thanks. Being able to do eveything from "scratch", rather then rely on software calcs is invaluable

[@apm]

Some of those analyses are only valid for linear relationships: stats.stackexchange.com/questions/220080/pearsons-correlation-for-non-linear-data There is a nice regression add-on to Excel called "Data Analysis" that does most of the basic analysis types for linear regression. Here is more advanced material for nonlinear regression (in Python): apmonitor.com/che263/index.php/Main/PythonRegressionStatistics

[@amirashkansaadati4480]

Thank you so much, would you please talk about the best way to find right formula for some nonlinear data? I mean when we have data, how we will be able to find their relationships based on formula?

[@apm]

If you don't have a particular physics-based form then you could try many different forms and see which one does the best at fitting the function. Otherwise you could use machine learning and let the computer decide the form. apmonitor.github.io/data_science

[@fowul]

Can this applies to multi variable non linear regression as well?

[@apm]

Yes, you can do multivariate nonlinear regression as well. The Excel solver won't do very well with big data or very nonlinear equations. You may want to try a solver package like Gekko in Python: apmonitor.com/me575/index.php/Main/NonlinearRegression

[@nicolasmoreno4990]

Do you know how to calculate the error of the parameters with excel?

[@apm]

Two popular ways are sum of squared error or sum of absolute error.

[@yuziwang7705]

Hi, thank you, it's very helpful. So we can actually use the RSQ function to check how good the fitting is even it's a nonlinear model, right? Because I was not sure if the RSQ could be only used for a linear model or not.

[@apm]

Yes, RSQ works for linear or nonlinear regression.

[@murrowboy]

Dumb question but how accurate is regression compared to just taking a moving average of previous values?

[@apm]

Regression gives you a model that you can use to predict future changes. Moving average could also be a future predictor but it suggests that future values are similar to recent values.

[@murrowboy]

@@apm I'm assuming this is a popular technique for stock prediction. I wonder how accurate it can be.

[@robinoconnor5760]

This was extremely helpful. Is there a similar method for multivariable regression?

[@apm]

Yes, but you may want to use a tool like Matlab or python apmonitor.com/me575/index.php/Main/NonlinearRegression

[@apm]

Here is more content apmonitor.com/me575/index.php/Main/LinearMultivariateRegression

[@oxydol3456]

Great video. Do you think it's not easy to create the solver-function clone from scratch?

[@apm]

A basic solver would be possible. Here are some guiding principles: apmonitor.com/me575

[@ajay4forest]

How to fit two way regression ?

[@apm]

Here is an example with 3 predictor variables: apmonitor.com/me575/index.php/Main/NonlinearRegression You can do the same thing in Excel, if needed.

[@samriddhatuladhar3913]

i can not thank you enough.

[@apm]

I'm glad it helped.

[@samriddhatuladhar3913]

@@apm What if i dont have the experimental values , how can i predict the values only with the equations ?

[@apm]

@@samriddhatuladhar3913 it is no problem if you don't have experimental values. You can just put your equation into Excel to calculate the predicted outcome.

[@waimyokhing]

please with matlab...

[@apm]

I repeat most examples in Excel, MATLAB, and Python. You can see the MATLAB content here: apmonitor.com/che263/index.php/Main/MatlabDataRegression

[@waimyokhing]

but in this example show the regression model. i want to know autoregression model.
thanks...

[@waimyokhing]

this is my nonlinear autoregressive model ...
x(i+1) = a*x(i) + b*exp(c*x(i)) + epsilon(i);

[@apm]

Here are examples of autoregressive modeling in Excel, MATLAB, and Python: apmonitor.com/do/index.php/Main/ModelIdentification I recommend that you look at the Excel example first because it is the easiest one to understand.

[@waimyokhing]

thanks alot for your reply i try it....