 So welcome again, same shirt, same ridiculous fluffy hair, now we're gonna go back to some more Excel skills So once again, this is an optional extra It's gonna be introducing you to something called non-linear regression and some least squares regression Entirely optional and some additional skills for how you would actually go about solving this kind of in reality so previously I covered how to Solve a first-order rate constant just by doing this linear method We just linearize the data and get a straight line in the gradients here So that's a pretty neat way, but what if the rate data was a bit more complicated? Well, you couldn't use that method, but you could use this method or at least a variant on it So what we're going to do is try and replicate this result using a different method and It's gonna be it sound a bit long-winded and trivial and a bit pointless and to a degree Yeah, it is when you can do this version but this is more extensible if you've got more complicated data and more More kind of rate data and more complicated reactions, this is a lot more applicable. So As before, let's just make the data nice and tidy I had this as seconds. So that's time in seconds. This is our concentration data and if we plot it we get That nice little decay curve that's Tangle and not very good. Um, so Remember we wanted to discard these last two data points. So we want to fit these four only So you can see there's a nice little decay curve there Now one of the things that allows us to do this is the integrator rate law We cover this a little bit later when we'll do a bit of data acquisition So if you'd know that rate is equal to the rate constant times a couple concentrations if you integrate that You get concentration much how like you take distance and differentiate with respect to time You get speed so if you take speed and integrate with respect to time you get distance again So the same thing speed of reaction you integrate you get a concentration And this is the formless log form that we are exploiting previously. So That's a linear equation Where the gradients is equal to k and the x value is equal to t and that log of the initial Concentrations are intercept y equals mx plus c. This version however is the exponential form But it predicts what the concentration is That there is the concentration at a particular time and so if we plot tail on the x-axis We get this nice little exponential decay curve. So that is a prediction of What concentration be at any one time and if we can get that for any particular set of reaction data No matter what it is if it's second order third order a bit more complicated There are more than one rates to solve with a lot of concentrations going everywhere. We can feed this into a simulation and Get the rate constants out and this can work for as many rate constants and as many entities as you're following Like so, I've got one somewhere that will solve up to five at a time five different things exchanging and dozens of different rate constants But for now, we're just going to do the basic version where we're just looking at one rate constant And it's kind of first order So I need two more columns here. I want my data and I want to call this one Calculator, so I'm gonna put my calculated data here Before we start that though, let's Think of what do we Need we need some constants to work out. So what we need from that equation is a zero so a zero equals And now just to Tidy that up a bit. I'm going to highlight it format cells Subscript there you go. It looks nice now and then k equals So Let's just stick some random values in there Now I've kind of pre-formatted these to be right aligned in slightly different colors to make them stand out If you have a system for what should be target cells or what should be input cells it makes things a little easier and What I want to now do is do a defined name job on these as well So I want to right click that cell define name excel things. I want to name it a underscore zero close enough for me This one I want to right click to find name excel wants to name it k again Good for me. So if you've set these up initially When you do to find name it picks them for you automatically great fun So now I want this equation to tell me this so For this, what do I need? I want equals and it is equal to my initial concentration a zero Now if you were to do this and drag down you would need to start using Those absolute references you would need to do d Oh, sorry One if you just use the named ranges You can bypass that entirely so it's one advantage of using them So I want that and I want to multiply that by e to the minus k two now you can't just type a At there for this for excel you need an exp function So that raises the oil less constant to a certain thing and I want minus k Multiplied by the time Let's close that one and if I let that go it's one Uh, that makes sense. Um, I've set my initial concentration to one Z uh time zero that should be the initial concentration. So it's one so that works perfectly fine Let's drag that down and what you can see is that goes down Pretty sharpish. So Let's click here and drag that graph across What you can see is that that is pretty quick. So what I want to change is maybe lower the rate comes onto bit Five maybe I want no point Five there we go. I'm not starting to see numbers now So we can see that here the rate is decreasing in the calculated one. That's the red Um, and the blue is the experiment data. Of course, this doesn't quite match. Does it? I mean the red is much higher. That's because our initial concentration isn't particularly high So let's change this to no point to five Uh, so that's a bit closer to here our rate constants of reasonable estimate Ah, and that's a bit slow. So it's increased that to 0.02 It's still a bit slow. So it must be a bit higher than that 0.005 Okay, it's starting to match. But how do we work out? um What's the right fit here? Uh, and we do this through something called least squares regression So at least squares regression It's kind of what's happening behind the scenes when you tell excel to fit a trend line It does it sort of automatically it doesn't tell you that it's doing it just as it fits it Uh, but what it's doing is simulating a y equals mx plus c graph and fits it to your data point So we're going to do that now manually Uh, but what we can now do is do it to some much more complicated equations So you've got a more complicated equation than this you can type it in Um Check it to your data and do any kind of fitting you like So what I want is difference So let's have a look at this the difference we want to Just subtract one from the other Let's kill a few of those decimal places And drag it down Okay Uh, now what you'll notice that some of these are positive and some of these are negative Let's drop them down. Uh Now that's not very good because that means if You have some data points that are above Your calculated values and some data points that are below your calculated values They will cancel out. That's no good to us So what we want to do instead of just that we want to square them So I'm going to put some brackets around those two cells and do the little hat sign to the two to square it And drag it down So now they're all positive So as the data diverges away from your calculated values, all of these values will go up whether it's below Otherwise and I'm going to delete those last two because as I said, we're not going to fit those last two values So we don't care about them deleting those mean the next bit isn't going to do anything to it So I want equals sum And I want to just highlight that entire column So that's now a sum of all the numbers above and what I'll do is I'll just highlight it and bold it so it's This is our target cell So this is a sum of the squares now the least squares in the word least squares regression refers to the fact That we bring this down to as close to zero as possible. So to show you how I'm going to do that I'm going to write like this give this a name and call it target for now Now you need to do something excel called solver and this is a tool that's really useful and It's you know, if you do more complicate complicated excel work You'll be using it quite a lot and it's found in the data tab of the new things you should off to the right I think my face in this screencast covers it, but it's called solver And here we are If you don't get this and you can't see it just go to google and type solver excel It'll tell you how to activate it. You have to go through options and the add-ins and just enable it and it appears It's really straightforward. Just look it up how to enable solver So solver look up Enable solver it will 101 different instructions. I'm not going to go through it So solver this gives us A set of parameters. So I've already installed some of them. Um, I'm going to do it again. Anyway, so I want to set objective What's my objective cell? I already called it target And I want to get that value as little as possible I could maximize it That would be ridiculous if I want to minimize it. I don't want to change variable cells So which cells do I want to change? Well, I've labeled them up here. I want to change a underscore zero So I'll start in concentration and I want to change k Now you might be thinking why do I need to change The starting point when I've got data here No, you don't have to You could just set it to whatever this initial value is The trouble is that sometimes That's not necessarily representative of time zero if you're doing an nmr spectrum For instance, you might scan over the course of a minute So your data point actually isn't a point in time. It's kind of an average of a minute So it's only really representative. So you want to give a little bit of freedom to that starting point And it's just kind of good practice to leave a little bit of extra wiggle room in there and what Solver is now going to do is it's going to fiddle with those values Until our target cell is zero. So it's pretty close to zero right now. I want to solve that I want to okay this keep solver solution So what it's picked up is that From the simulation our initial concentration is actually 0.0266 the k is 0.0082 And look these two lines now fit quite nicely So that red line will be a perfect exponential decay The blue line will have a bit of noise associated with it But now we've got k is equal to 0.0082 uh That's not too bad. We've got 0.0079 for the one done this way And 82 for the one this way. So that's you know, pretty close If we've got more data points, this is remember only fitting to four and that's quite quite a shallow curve there We probably get very similar results But because we've knocked off two and we're only fitting to four data, they're likely to disagree by a little bit Not by too much certainly within experimental error So there's a value we can get from here now again the advantage of this method Using solver and trying to get this is that you know, we could have k1 k2 k3 and solve multiple rate constants for a lot more complicated data If we wanted to uh, or we could do none First order reactions and with two concentrations changing So it is a much more powerful and more extensible method so Here we just take advantage of taking logs. It's a really straightforward thing You can plot this by hand if you like. You don't need to do it via a computer This simulation method is much more powerful at least in my opinion. This is how I would go about doing it And it's how I've gone about doing it in the past as well. So find solver Set yourselves use the integrated rate law to predict the data Find your least squares regression, which is the difference between the two values squared Total them up with the sum function here And then you solve it minimize that and you will get a nice decay curve that matches your data And this is probably pretty good for a rate constant. No point. No no to it matches what we've got already So if you've got something from that great, uh, if you skipped it, you're obviously not watching this part So we will see you in the lecture and we will then move on to doing some other topics So I think this kind of wraps up the whole thing we need to know about rates right now. So Congratulations if you made it this far, uh, and see you in the next lecture