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.
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.
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.
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?
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
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
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
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.
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
@@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.
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.
I learnt a lot especially that point where you imported data.
Wow this is awesome, thank you for making this! Didn't know that solver could make it so easy
It is a great tool for small to medium sized problems in optimization.
Very useful video, I did it with a "complex" curve and fitted it very well (impossible with polynomial equation)... thank you
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.
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.
Thank you very much, it's a fantastic tutorial, I love it.
Thank you so much, well explained
Dumb question but how accurate is regression compared to just taking a moving average of previous values?
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.
@@apm I'm assuming this is a popular technique for stock prediction. I wonder how accurate it can be.
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?
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
Excellent video. Thank you.
7:34 Very good explanation, thank you.
Thank you so much! Very helpful.. God bless you :)
If I want to do fittings on a 2Y graph on excel. What should I do?
For multiple regression try Python: apmonitor.com/pds/index.php/Main/RegressionOverview
Thank you so much. really helpful
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
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
Very helpful thank you
Thank you so much
This was extremely helpful. Is there a similar method for multivariable regression?
Yes, but you may want to use a tool like Matlab or python apmonitor.com/me575/index.php/Main/NonlinearRegression
Here is more content apmonitor.com/me575/index.php/Main/LinearMultivariateRegression
Great video. Do you think it's not easy to create the solver-function clone from scratch?
A basic solver would be possible. Here are some guiding principles: apmonitor.com/me575
THANK YOU VERY MUCH
Thank you so much!
Excelente, gracias!
Well explained.
Thanks for the encouraging comment, Nalin.
thank you man you are the best :D
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.
Yes, RSQ works for linear or nonlinear regression.
Can this applies to multi variable non linear regression as well?
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
Do you know how to calculate the error of the parameters with excel?
Two popular ways are sum of squared error or sum of absolute error.
i can not thank you enough.
I'm glad it helped.
@@apm What if i dont have the experimental values , how can i predict the values only with the equations ?
@@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.
How to fit two way regression ?
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.
please with matlab...
I repeat most examples in Excel, MATLAB, and Python. You can see the MATLAB content here: apmonitor.com/che263/index.php/Main/MatlabDataRegression
but in this example show the regression model. i want to know autoregression model.
thanks...
this is my nonlinear autoregressive model ...
x(i+1) = a*x(i) + b*exp(c*x(i)) + epsilon(i);
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.
thanks alot for your reply i try it....
Thank you very much