How to fit non-linear equations in excel using solver
HTML-код
- Опубликовано: 9 фев 2025
- Tutorial of how to fit non-linear curves in excel using solver. This tool lets you fit custom equations to your data. For example in this example we fit data to a modified version of the Avrami equation.
Thanks to Ozkar and Finke for their data and if you want to learn how to grab data from papers using WebPlotDigitizer, check out my video tutorial.
• Tutorial of WebPlotDig...
Very helpful. One thing to note. The real reason we square the residuals is to ensure that that we are not minimizing the sum with positives and negatives. Being on either side of the fit is an error, but if left unchecked a residual of 0.1 and -0.1 would equate to zero when in fact they should be agnostic to which side the error occurred and sum to 0.2 . To fix that we square the values and everything is positive, thus finding the "least squares" gives us the best fit. I don't know if that is what you were implying in the video, but I did not hear that called out.
You are absolutely correct. Thanks for pointing that out.
As a researcher myself investigating heterogeneous behavior, this short tutorial was extremely helpful. Brilliant explanation, Subscribed!
Thank you!! Let me know what else would be helpful!
@@TaylorSparks Is there any other way of further optimising the solvers fit within excel?
@@michealcairns4971 I'm sure there is. I'm not an expert in using Excel's solver though.
I have watched so many videos on how to do this and this is the first one that actually made sense and where I actually understood what components went where. Amazing! Thank you so much!
Feedback like this makes me so happy.
I'm here from July 2023 and I'd like to say that this is EXACTLY how neural networks are trained. I watched Andrej Karpathy's full coding of ChatGPT and this is exactly what he did there. We have a certain expected output from the network. But the real output is different. We measure the difference i.e. residual i.e. error and work on minimizing that. That's literally all there is to it. The code is just there to make this process easier and find each parameter for billions of neurons.
Yeah, a lot of people think machine learning and AI are more sophisticated than they really are. If you are interested in materials informatics I have a whole playlist from a course that I teach here at the University of Utah. Check it out
@@TaylorSparks Wow, didn't expect you to reply so quickly. Or at all 😂 Thanks for the video. It really helped me out. I'll check out your other stuff too when I get the chance 😃
Great explanation. Straight to the point and without missing any necessary details. I'll look at your videos for future questions.
You made it very easy. In order to fit a model, bring any equation and very the parameters then go to Solver to minimise the sum of the squared Residuals (SSR) and this gives the best values for parameters.
all these years I've always used Matlab or Python... Often find myself in Excel... this will work well. Thank you!
This is amazingly helpful! I watched numerous videos trying to do logistic regression fitting and this is the only one that helped me!
So glad to help!
Same happened to me. Thank you.
Same for me
bruh this explanation goes hard it was so clear and concise, I've tried to read how to use solver from other sources but they were always convoluted and not too useful. this video in contrast was good and efficient thank you!
I gotchooo boo
Great job leading us through this so smoothly. I suspect that most people coming here are like me and know what they want Excel to do but had no idea it could do it. It took me about 60 seconds to learn how to use solver from your video, as opposed to never on my own. Thanks!
I'm so glad it was helpful!
Hey, Taylor! Just wanted to thank for your all work, from material science to statistics!!!
Thank you so much! Do me a favor and check out our podcast Materialism. You can find it on any platform and we would love to get an iTunes review if you've got a second. ;)
You explained it in 6 minutes better than my docent in 2 90min sessions, thank you so much!!
Glad to help dude!
Thanks, you literally saved me trying to interpolate a sine wave to data that looked like a sine wave. Again thanks!!!!!
My pleasure!!
This is one of the most beautiful video on youtube. God! It's so good.. Thank you!
Lol. Glad to help friend!
Dude, helped me out so much, thought I had to redo all of my measurements for a uni project. Very well done!
My pleasure! Give the video a like, sub and share and it will help me keep making content like this
ruclips.net/video/-rW0q11JMag/видео.html
Great video. There is a lot of people out there doing super complex explanations for this, but this one is perfect. Thanks a lot.
Thanks dude!!
Explain everything in very easy manner... Nice job
Heck ya
a good solution to find the equations for solving "the area of hysteresis" problem. Thank you so much
Great point. Very happy to help.
My english listening is very very bad, but it was no a problem for I reproduce your procedure, excellent, thank you so much bro
Super glad to help!
i LOVE THE VIDEO, I ASKED MANY PEOPLE ABOUT IT, BUT NONE COULD EXPLAIN ME BETTER. Thank you
Glad to help!!
You’re amazing boyyyy!!! You just solved one of my chemical engineering project .Thanks
Glad to help!
hi fellow chemical engineer
Thank you for help.. I tried to obtain wavefunction (800-data). Result: Great!! thanks 🎉🎉
Thank You, Taylor. I was trying to fit using MATLAB. However, your video is awesome
Super glad to help. Thanks for tuning in.
Thank you! I just started a statistics class and I did not understand the residual until I saw it happen, visually, on your graph.
Short, direct and extremely helpful
Glad it was useful!
Absolutely excellent, enjoyable, clear and precise. Well done sir!
Thank you so much. I'm super glad to help
I never knew about plot digitizer before watching your video and going through the description
It's so great
Thank you for mentioning that the Solver needs to have a initial best fit that is somewhat close to the existing data! I tried this method after watching another video, the author failed to mention this and I was very frustrated after spending over an hour going thorough my excel sheet looking for problems that were not there.
Right? Makes a big difference
This is the best video that explained the regression fitting. Thank you!
🙌🙌🙌
Thank you for the clear introduction! Like another commenter I am trying to fit dose response curves which all have starts of 0, and it goes very odd, but you've got me further in a few minutes than I expected and opened my eyes to what's possible in excel (I don't get coding, despite trying to learn).
thank you so much, Sparks. now I can defend my thesis!
wow! I'm so glad to help! Good luck on your defense.
Awesome video! Helping me with my studies. Thanks a lot!
Perfect video! Thanks for your great instruction
@@roykeyer2174 Happy to help!
Great explanation! Clear and straight to the point. However, the main problem with fitting curve is to find the potential fitting equation to work on :)
Ya. There's no substitute to some basic mathematics. They really are helpful when getting an equation of the right form.
Thank you so much. It helped me a lot. Your explanations are simple and very efficient.
Amazing video, love your work, saved my chemistry degree xxxx
Woohoo!! This feedback makes me so happy. You might like our materials science podcast "Materialism." Drop us a review if you can.
Excellent video - I now use the solver option a great deal in my work. Thank you.
Thank you. Saving a students life rn.
ruclips.net/video/-rW0q11JMag/видео.html
this make me more understand about sigmoid value. Thank you!!!
Heck ya homie!
Thank you 🙏 solve my question within 6 min lol. I’m trying to do my homework for mass transfer and needed this
Pleasure to serve!
Its great video. Very short and only informative. Thanks
Glad to help!!
@@TaylorSparks I am looking for parameter estimation for a system of ODE direclty without finding its solution. I would be grateful if you suggest anything to me.
I have one word. Beautiful video. Thanks
Heck ya!
Wow.... you teach me what i really want. Thanks a lot!!!
This is really helpful.Thank you so much.😃😃
very helpful,keep uploading this type of content
Got a specific need?
Thank you. Great Explanation. Very helpful
Absolutely awesome video. Clear and to the point. Great work mate.
Thanks, very useful and quick video.
Now this is magic! Thanks alot, very helpful!
Glad to help! Please like subscribe and share!
Yes! Thank you! I will be using this for my research as well as for my own educational purposes of modeling covid-19 confirmed cases
That's wonderful!
Got here for the same reason. Thank you, Taylor!
Reported covid19 cases are forged...
Jannie Schlüter Wear an aluminum foil hat. Problem solved.
@@tecnocato you already wearing it. Nothing gets through your thick melon. 🙉
Mr. Sparks thank you very much!! You did me a big favor!!!!!!!!
oh oh awesome, I'm always glad to help. Do me a favor and subscribe like and share. I'm trying to grow this channel so that more people can find it.
Wow thank you so much...i now have a clear understanding of this work
This helped me A LOT! Thank you!
Glad to help!
amazing video! Very good job!
Thank you Taylor for sharing this very important video
Glad to help! Sub to see more content.
Greatest curve fitting vedio.. Thank you. 🌹 🌹
Why thank you!
@@TaylorSparks you did great jobe. You deserve a big thanks. 🌹
wow very helpful demo, thank you!
THANKS! Your explanation was super clear!
Glad to help!
Really helpful video I used this in conjunction with Gompertz forecasting equation and found the optimal constants for forecasting shipment nos
Glad it helped!!
Helped me a lot, thanks
Wonderfull video!!! Very precise and usefull information. I want to try doing this using Origin or R, but I need a video like this.
ruclips.net/video/-rW0q11JMag/видео.html
Thanks a lot, very helpful. I got it
Hi Taylor, thank you for such precise explanation. I want to know how can we find uncertainty in fitting parameters and eventually the goodness of the fit?
Thanks. I am using this for my thesis regression analysis!!!
It was very helpful, thank you
Well explained. Thank you.
💪🏻💪🏻💪🏻
very helpful, thank you sir!
v.v. helpful thank you bro
That was a fantastic video. Great presentation. Thank you
So glad that it was helpful! Let me know if I can help with another topic. I also appreciate if you can like and subscribe to help me grow my channel.
This video helped me so much thank you man
You are a legend.
Thank you so much, great explanation.
thanks a lot its very helpful i really appreciate it 🙏
Glad to help! Check out our podcast Materialism available as a playlist on my channel 😁😁😁
THANKS! Your explanation is awesome! It is helpful!
Very glad to help
well explained , Thank you
That was a simple and straightforward explanation. Can you say how did you assume the equation?
From domain knowledge. I've seen that shape before so I figured it would work.
Very helpfull tutorial, thanks!
ruclips.net/video/-rW0q11JMag/видео.html
Thanks Taylor .
That's very helpful:)
Thank you sir
Nice explanation
Excellent!
Great video
Fantastic video and explanation. Please let me know if you are familiar with other functions similar to Avrami without an upper bound. Thanks!
Just exp(x)?
This is f*cking great!!! This helps me to pass my assignment
Glad to help homie
very helpful!! thanks!!
This is very helpful! Thank you so much...
Thank you. This is very useful!
I benefitted. Thank you
ruclips.net/video/-rW0q11JMag/видео.html
Helpful, thank you so much
Hi Taylor, this video is very helpful, thank you. Just have a quick question, for inhibitory non-linear response curve, what equation should be used?
Thank you for you comprehensive video. I have a simple question, How can we define the error or standard deviation from the fitting constant A, k, n ?? e.g. A+/- something, k +/- something and so on.
This was so helpful! Thank you!
Love the way you explained. Very clear. Thank you so much,
Glad it helped!
so so great, thanks a whole lot
Glad to help
very nice work good work are there any other videos presenting the same ideas?
Gracias hermano! me ayudaste!
De nada che!
You mention in the beginning of the video that Python would be better software to use to do this kind of fitting. I liked this video and it was helpful but I would like to see you do tutorial on how to do the same thing in Python.
ruclips.net/video/HWwLZP14DG4/видео.html
@@TaylorSparks Oh perfect, thank you very much!
This was really useful. If i've calculated the fit how doi calculate the upper and lower limits at 95 % confidence level in excel?
Thanks Taylor for the video. The solver in excel i understood well from your explanation. Can you please elaborate more that how i can start with the Y_fit equation just by looking at my data set ??? i mean you choose the exponential equation for your data set ???
Ooh, that's the hardest part! Unfortunately there's no easy shortcut for this. Technically, you can try symbolic regression to try many functions until it finds one that fits well, but mostly you have to do some math text searching.
Great vid thx man!
Thanks dude! Like, sub, and share to help me grow. What else would be helpful to see?
Thanks! simple, and very useful!
Thanks! Like share and sub so I can grow the channel!