 This video is about solving kinetics problems with non-linear regression, now you can implement this in various ways, this is going to be with a spreadsheet but the maths is kind of generalizable and you could code it up in whatever you like. So let's have a look at how you would do it, save from lecture notes for a moment. Say you've got this data, you've got time and you've got a concentration and that's maybe a product disappearing to become, sorry, reactant becoming a product, you get these characteristic curves and the way you would do this conventionally is that you take advantage of the fact that the first-order rate law can become a linear graph really easily, the rate is equal to kA, you integrate that to get a characteristic plot of what concentration should be and it's an exponential curve then you take the log of that to linearize it and that's what we're going to do here, let's take a and we type in equals ln for natural log of a, turn that, shoot it down for some reason this has gone into percentage so let's get rid of that and then we can chart these numbers, insert a single graph and it's a straight line, add the trend line, add in the equation and we can see that the rate constant here is 0.35, we can do some more linear regression stuff, we can get that out of the slope function if we wanted to and that's fine, that works but sometimes real data is a bit more messy, sometimes things can be a lot more complicated, we're not just going from A to B so let's have a look in this data for instance, now I've got A and B but let's plot this and see if there is a slight difference, you can see these are not going to completion, in fact these are going to about maybe this is settling off at about 10 or 11 and this is only going to 45 so maybe about 80% completion this is going to and now that is an indication that there's an equilibrium being formed, we've got a rate constant going from A to B that's forming the product and then B to A going back so we've actually got two rate constants to solve and it's not immediately obvious how to do that from this data so what we're going to do is set up the simulation and then optimise that simulation and match it to the data so there's going to be a little bit involved but stick with me we will cover the maths quite slowly now right so that's the linear method it really requires only kind of special data so let's have a look at the non-linear method and this requires kind of making a bit more of an assumption about what this D by DT means so what we're going to say is D A by DT what does that actually mean in terms of the calculus well it actually means a certain amount change in your y divided by a certain amount of change in your x direction and the D by D terminology means it's a limit so we're actually going to be a bit more specific about that next explicit we're going to say that concentration of A at some time plus what we're going to call delta T some time interval minus that same concentration at time T so that's what D A means and if you're familiar with your calculus and this should not be too out there this is this is exactly what this symbol here on the left means it just means that delta T approaches zero it becomes this and we're going to stick at the bottom delta T so some time interval and that is still equal to the same thing as before K times the concentration okay and I'm going to be very explicit and just say that it is at time T so at a particular time the concentration is this the concentration is this and then in a future time the concentration is here and that future time is determined by that step delta T now we just need to do a little bit of rearrangement we're going to send the delta T to the side the other side and it's going to take a little while to write out because I've insisted again I'm using multiple colors for this but it keeps track of everything times all in purple concentrations in red there so we brought that to the side and this actually makes some sense now because what we have is rate constant which first order rate constant will be in per second DT will be in seconds and this is going to be in concentration so what we've got here is a unit of concentration now what would be left with if we've moved this is this here so we can rearrange this to the other side so we're going to plus the concentration at time T which is in molar so now these units are all compatible with each other per second and the seconds cancel out because we've got a rate constant in per second multiplied by a time step and that leaves molar from this and molar here so those are compatible and what they'll give us so if we rearrange it it's time this will be the concentration at time t plus delta T so this is we're going to do we've actually kind of simplified things a little bit and this is what we're going to do to build a simulation so I'm gonna go back to our spreadsheet and start building a simulation using that equation so I'm gonna give myself plenty of space over here gonna write time a but you could label these as predicted if you want but it's not entirely necessary at this stage so I'm gonna start at time zero and we need to figure out a prediction for these we'll come to it in a second actually let's we know what time zero it's going to be it's actually going to be that number and that number and now we need to add a particular amount so actually before we do that I'm gonna actually set up the variables properly because these are going to come into play so we want a rate constant which we're gonna call k a b that's the forward rate constant and I'm gonna set up a backwards one called k b a that means we're going backwards we want a rate constant there we also want the starting concentration we'll probably add this into the simulation later we can fiddle about with it and I want b 0 and also want delta t and what I'm going to put in is dt and I'll make that a 0 1 the starting concentration to be I'll make 0 the starting concentration about a will be 55 arbitrary units at this point you probably want to probably wouldn't get to 55 molar in reality but it's it's an arbitrary unit and I'll stick these as one and because it's Excel I'm gonna highlight all of these go to formulas create from selection and in the left column so I'm now going to have these values saved so that's I've been equals k a b I get the K of the constant so now to build up the simulation I'm going to add to the above time dt which is that so this is useful to do because I can just change this step if I wanted to if I wanted to make it smaller and a more accurate count more accurate simulation I will make that smaller remember this whole thing this linear method is actually the limit for when that time step hits zero so the more accurate your so the smaller that time set more accurate your simulation will actually be but you don't necessarily need it to be super small just at least a tenth maybe up to a hundredth of your overall time I'm just gonna keep zooming this down I'm gonna need a lot more rows it's shut it down to about 14 it doesn't matter when it ends it just matters that we've got it labeled correctly I keep a scroll up right so there are my times and now I'm going to fill in this so I'm gonna come back to the this for a moment and just talk a bit about chemistry so if we have a exchanging be going back and forward like that then a is going to be produced by whatever this K be a race constant is and it's going to be removed by whatever this K AB constant is so we look back on here we know the changes K the rate constant multiplied by the concentration multiplied by the time step so concentration of a keep it colored why not at time t plus delta t it's going to be equal to its concentration at time t and it's going to be increased so I'm gonna plus I'm just gonna put these on different lines but it's it's all been added together it's going to be increased by K be a that's this equilibrium this rate constant going backwards times by the concentration of be because again be is having a first order reaction going in the other direction exactly the same thing multiplied by delta t and a is going to be removed by the forward reaction minus K a be as going to be based on the concentration of a as it is first order again times delta t and this is where this method becomes really powerful because you can extend it as far as you like a could be exchanging with be it could be exchanging with C exchanging with D it could have all sorts of equilibria going in and out of it you just bolt that on here as long as it's roughly first order or pseudo first order going in and out you just bolt on another piece that looks like that and that's fantastic that means this can go as complicated as you like you can have as many things exchanging as you like bolt another one of those on to take it into account so let's actually implement that so I'm gonna remove these just for a moment and replace them with equals a zero and be zero so I'm only gonna fiddle with these I'm gonna highlight these as let's change them to impose styles so what's this next time step going to be I'm gonna be equals and it is going to be the previous one previous concentration at t and we're going to plus kba fantastic because of we labeled them up it starts the excel file starts to look like equations you don't have to start dealing with cells or anything like that multiplied by the concentration of be it's gonna be that one there because that's the concentration there's no be there so this is actually not going to add anything multiplied by DTR time step and for some reason yes again percentages what did it switch to that we could shoot that down and no change because there is no be here if we change that to a one look it drops down but it's also gonna be minus something so it's gonna minus kab that's the forward reaction that's a removing a multiplied by the concentration of a previous time step multiplied by DTR time step there what we've got there is let's actually just plot that very quickly we've got that neat exponential curve or roughly exponential curve that we'd expect from a first-order reaction but it is going to completion and the reason it's going to completion is because the concentration of be remains zero so we're going to have to be to this now it's exactly the same as before we're going to figure what's going to change to be well we're going to start with the previous one and what creates be well it's going to be kab plus it multiplied by concentration of a because that is generating be by a time step and be going to be removed by its previous concentration multiplied by kba that's backwards one by DT so you can see after the first step there is a little bit of be present now because there was 55 arbitrary units of a by a tenth of that is converted now we can shoot that down and you can see how those numbers kind of briefly interact with each other if you go back and see those change now if we add this in let's look at this there we go that is slightly different now this is what you expect to see when things go to equilibrium they don't quite finish let's just move this up to the top now what happens if we double the rate constant that's going forward we see the equilibrium begins to favour the product if we set this to zero it goes completely to completion if we set this one to be a bit higher then it doesn't really go past 50% and we set these constants to be smaller it's a slower reaction and you can see kind of the ratio of those rate constants kind of determine your equilibrium constant as well so that is the simulation that we're doing the next part involves the actual regression part of it so very quickly you can delete that and I'm going to add a new series to this so we can actually see both on here so the series x values that's going to be all the way down here that's the time the y values are going to be all of this so you can see that's our simulated product and practice name it simulated a that's a simulated reaction I don't know this I'm gonna be so we can actually now see these on the same graph and because there's more data points here these look a lot more continuous but if we want to format this data series we can actually change the marker options to be no marker but to add a line instead because that's in theory it's continuous and what we could do is we could change delta t that time step to say 0.05 it would be a shorter simulation we'd need to go back and add a few more rows but it'd be a more accurate simulation so maybe hold just leave it at 0.1 for now because it's good enough so now let's get back on to non linear regression what's that about so if you look at linear regression or any kind of regression technique it's based on the idea of fitting an ideal line to some data points so let's imagine those dots are our data I'll stick those straight line for now and the straight line is our idealized prediction but what we need to do is need to understand what's the difference between this point and the simulated point we could measure all those distances what is that distance between the line and the point and we could add all of those up except two problems doesn't necessarily weight it very well and to anything that's negative here will be compensated by the positive ones we don't want to do that so we take the point one let's call it y well we subtract y prime which would be doesn't matter which way around one of them represents the idealized line the other one represents the data point literally it doesn't matter which way around you get these and you square it that's why you don't it doesn't matter because it doesn't matter which way around you do this this body will always end up as positive and so you just sum them all up so that would be the mass notation of it and you want to minimize that you change the parameters going into your simulation to minimize it so let's set that up now move these over to one side for a moment what I want is I want to do it entirely up here I'm going to ignore this whole simulation off to one side for now don't need to worry about it so I want the value of a predicted so we're going to add a V lookup to do this so you do V lookup X lookup if you're in the newest versions but I'll do V lookup for compatibility reasons and so what this is going to do is it's going to try and look up a value which is going to be the time in a particular table so I'm going to set that to our time and the table array is this one here I know that goes down to 144 and it's the screw it doesn't enjoy scrolling very far when I'm recording at the same time and the important thing here is I'm going to have to set that to be locked using those dollar signs because I'm going to drag this down in a moment I don't want to move that table array so V lookup is going to look up this number in blue and it's going to look down the left most column here a couple of other ways of doing it but I'll do V lookup because it's quick and it's going to return whichever column so if I return I type one it's going to return the first column if I type two it's going to return the other column so let's do that I'm going to shoot it down there we go for some reason if I type in false here for exact matches stopped working so I don't know why did that so I'm just going to leave it as is and what you can see is I'm returning these values now so it's looked down for time equals seven and I scroll down to time equals seven 18.38 it's returned 18.38 great that's what we're after I'm going to do the same be predicted don't equals that do I and I'm going to do the exact same thing so I'm going to copy the formula paste it in and change that two to a three because I'm going to return the third column along and you can see returns zero and it keeps increasing so that will be that will remain the same even if I start changing this because I've done a V lookup it'll find a different point in the table but it will match the the correct time make this really fast it'll probably yeah it'll eventually break because I can't quite find them so gonna make sure that it's at least returning something within the data you've got there so now let's do that nonlinear regression thing let's find the difference between the predicted point and the actual point so one minus the other I'm gonna wrap it in brackets and square it so I can even write it down there just to make sure I'm doing doing it right now where to do it for B as well one minus the other remember to wrap it in brackets and square it and all the numbers are positive and then alt equals just to auto some more of that that's in the sum and then I'm going to add in equals that plus this I'm gonna right-click and define this name as target okay so let's set this as an output so what happens when this data fits this number should go down so I'm gonna change my predicted equilibrium constant my rate constant I think in general they are going to be slower than that you can see those thick blue lines they're reaching equilibrium too quick so I'm going to change that to point three and point two mmm it's actually coming to more towards the it's actually producing more products so I'm going to lower that one and what you can see is now that's a bit better fit this is dropped to 180 and you can keep changing these numbers however you want and it usually helps to get it roughly on for what the next step is because we're going to solve it by changing these numbers automatically so I'm gonna come to the data tab and I've already got it set up and it's called solver you can just write target there and I want to change k a b k b a now you can also if things get a bit more complicated you can start adding constraints in you add a constraint such that a plus b must equal a constant and it will work it within that range so let's solve that turns through it and what you can see there's those two lines and how fit through that data really really well and that that numbers dropped from hundreds to 15 it would be really hard to fiddle with all of these data points and change it manually to fit so that's the basics of it we have to set up a simulation based on these constants the initial concentrations are optional it's nice to let them be fiddled around with slightly to give you a little bit of flexibility but it's not essential especially if you know the starting concentration and you know that the other should be zero it's more important to have these change the two rate constants what you can see is k a b going forward is point two five kb a is no point naught three and you can see according to this data it actually wouldn't necessarily hit equilibrium for another few more moments the data is it's still going up so maybe if you kept this going a little bit further you could refine it a bit more but that is it now how you do the errors on this and the uncertainties as a whole other thing that will maybe will require another look at but this is the basic outline and the reason that this is really useful is you can very very literally do anything you like if you if you have let's say a and b and c interchanging with each other you just bolted on the change in a it's going to be plus kb a times b plus kc a times c or multiplied by delta t and the same thing will happen you can do b you can do c you could set some of them to zero to say oh well we're pretty sure that doesn't happen so we're just going to deal with this or maybe we allow this to happen and see what the difference is you just keep solving it so that is non-linear regression and excel as I keep setting these things to percentages I've so far never clicked that button um that's it hope it becomes some quite sort of people