 Well, new t-shirt, new day, and let's do some more screencasting for kinetics. This is going to be predominantly Excel skills, so we're going to deal with what we've covered previously. And specifically, how do you want to do it in Excel? Now, different people prefer different kinds of software, so maybe someone in a physical lab will ask you to use something else. I like Excel because it's kind of nice and extensible. You can do more things with it. There's a few things that special software does faster, but you can kind of replicate it with Excel eventually. So I like it for doing this. Now, I'm going to cover how to solve a rate constant for a first-order reaction. So the theory behind that was covered in the last screencast. If you're happy with that, that's fine. If you want to learn a bit more about the skills you can watch this video, otherwise it's optional. I'm also going to do two methods. One is going to be straightforward, manipulate the data one, and the other one is going to be by simulation. And I think given the amount of time I've got right now, I will probably do the simulation one as a separate video. I will see how long I've got. Yeah, I'm flying here. So the first thing we want to do is get our data. We have time. We've sat diligently in the lab and recorded the concentration of something at a particular time. Okay, so here we have time in minutes. Every two minutes we take a concentration sample. Now, the first thing in Excel, we want to make the data nice. So everything is centered. I've put some headings in, and the headings are really important because if the data looks good, you can deal with it better. And it's underlined, and it's centered, and I've got a consistent number of decimal places. So I've got a few more complicated numbers here, and I can increase or decrease them. Just whatever looks good. I will set it to that. So once you've got all this data, what does it look like? There are two ways you can figure this out. One, you can graph it, or another interesting one. Let's just highlight all those. And if you go to this conditional formatting option, really older versions, like pre-2007, pre-ribbon ones, have this hidden way elsewhere, and don't have data bars. So if you go to conditional formatting, come down to this data bar option and just pick one. Whatever looks nice, a nice blurred one there. You can see immediately, at a glance, these numbers are decreasing. In fact, you can do a little formatting trick where you can hide the numbers completely and just see the data bars. That's really useful just to visualize the information. You can now see immediately, this is jumping down quite exponentially. You can also see there's a little bit of an increase here that might otherwise not catch your eye. We're only dealing with a handful of data points here, but if you were using a massive dataset, this is invaluable to see things like that. Now there are a couple of other tricks I want to cover. We're going to, for a start, we're going to highlight this. And while we're doing this at the moment, it will become clear. And we're going to right-click on it and go to Define Name. So I covered this previously, but I'm going to show some things like T min. There we go. Okay, that. And now I want to highlight this and go right-click, Define Name. And now what you will notice is that Excel is quite good at picking names here, but it will try to convert them to things that the VBA that it runs on will recognize. So it's stripped out things like the brackets and the slash and just called that A. So now if I type things like go up to this top corner and type A, it highlights itself for me. If I look at T min, it gives me that. It's still got the air conk and the rate from the previous sheet when I did this. So if we now highlight all of these or we select that and insert a graph to see what it looks like. We want to just insert, scatter graph, just a bog standard graph and bring it up. What we can see is there's a nice little decay curve there that we're interested in. It's quite clearly probably first-order kinetics by the looks of it. There's a little bit of a jump at the bottom, but otherwise it's nice. But this isn't particularly useful for us. What we want to do is take a log of it. So now I want to delete that to get out of the way. I'll show you how we deal with this and why I've enabled the ranges. So I'm going to do T sec here and then log A. So these are the things I want. And I'm going to do this thing again. I'm going to highlight all these cells to find them. And I'm going to call that T sec. And the same thing here. Right-click to find them LNA. Okay. This is just so that if we want to select all the data again, we just go up to here and click it. Now all of these cells are selected. Now if I wanted to, I can just type in equals LN. Stick that cell in. Close it off. Track it down. There you go. All those values are fine. But I want to just show you a new little trick in context. This is useful for really big sets of data when you want to change a lot of things at the same time. And they're called array functions. So it's going to look a little bit trivial. Like this operation, it's made seconds just there. I'm going to show you a slightly longer-winded way of doing it. But again, this is useful when your data is huge. And you will be dealing with much bigger data. So this is really a useful skill to get used to. Now I've selected my log A column here. So all those cells are selected. And I'm going to type in equals LN. But I don't just want an individual number. I'm just going to select a cell. Which range do I want to select? Oh, well, I've already given it a name. It's A. So let's just close that off. A number. Now, I don't want to press return because this is an array formula. And array formulas are kind of protected in Excel. So you have to edit them in a certain way. And the trick for this is to press Control-Shift-Enter. So it's Control-Shift-Return. There you go. Basically, I've entered all six values simultaneously. Now if I try to start deleting one of these, it throws an error at me. It says I can't do it. If I want to change the formula, I've got to highlight the whole thing. Press equals. And then maybe do A times 2. Then go double-concentration. Or select a single cell. Type A plus 5. Control-Shift-Enter. It updates it. So if I had this data that was huge and it went on to several pages, or there were hundreds of data points. I've named the range. I just need to either select it or select one single one. And then type LNA again. Control-Shift-Enter. It updates the whole thing. So I don't need to then enter it once and drag it down for several pages. It's really quick. And I'm going to do the same thing in here. I'm going to convert this time in a minute to seconds. So I want to select my T second. So all six of those cells are selected. And then equals T minute multiplied by 60. Control-Shift-Enter. There we go. Times and seconds and logging. So previously I highlighted all of those to make a graph. Now I want to highlight these to make a graph. So I'm going to insert again. Scatter chart. Basic scatter chart. There we go. That's a slightly different looking chart. And you can see there's a straight line. The data we played about before in the last kind of qualitative lecture type thing. And on this I want to right click it and go to add a trend line. Now older versions of Excel kind of throw this up in a separate window. The newest version that I'm running on sticks it off in the side bar here. So my face is over the top of it. I'm sorry. We want to select the linear option because our plot needs to be a straight line. And we want to display the equation on the chart and display the r squared value. So the equation is going to give us the gradient and the r squared is how good the fit is. So that is fairly reasonable. Except you can kind of see that these two last bits of data here don't quite work. We don't want them there. So we kind of want to eliminate this. So what we want to do is take the graph and instead of highlighting all those, we just want to lob off those last two. There we go. So now we've got r squared of 0.9935. Now we can do it again. We could actually select that data and for grab these boxes and reduce them just to a few data points. Yay. We've now got an r squared of one. So it's a perfect fit. But of course it's a perfect fit. We're only fitting two data points and getting a line between them. It's that's kind of pointless. What we want is a little bit of that noise and a bit of that error because this is random experimental error. Some of these data points are going to be higher than they should be. Some are going to be lower and hopefully that should cancel out. Now it does apply to a bit to our random error, but we'll discuss that separately. But we want that data to help cancel out. So we need, if we're going to start eliminating data points, we need a logical reason for it. And the fact that the fit goes higher is not a good enough reason. We need a good reason to eliminate those two points. Our first reason is that they are well off that trend line. So eliminating the last two should see r squared up to 0.99. And the other reason is that if this is pseudo first order kinetics towards the end of the reaction, that second reagent is going to be depleted. It's going to deviate away from first order kinetics. So we're actually, if we have that, you can see these last two data points really don't fit. And we also have a theoretical reason for why they shouldn't fit. And so we can drop them off. And there we go. There's our trend line. Now we've got it in the trend line. We can copy and paste that number. But again, I want to show you how to do this with just a formula. And it says I want to type equals and I want to type in a function called slope. And this gets us the slope of a linear regression line or a trend line. So let's open the brackets and see what it's asking for. It's after known y's and known x's. Now it specifies that these are known because there is another formula that extrapolates and gets you unknown values. So these are known unknowns that you want. So we want known y values. It's these log a values. So let's just select all four of those, comma known x's. Now we want to select all four of those. Close that bracket off. There we go. We got 0.0791. Exactly the same as this, but actually to as many significant figures as we care to grab it to. And now we could then input that into other equations. Say if we do this several times, we've now got a number here we can play with. So that's how you would solve this in Excel. Fortunately, I don't think I've got time to go into the simulation method. So I'm going to stick that as a separate thing. Again, that's almost doubly optional because I'm not going to be interested in it as far as this course is concerned. I just think it's a very interesting skill to learn. So let's briefly recap how we did this. The point is to keep your data tidy. We want well-labeled columns. If you want a quick idea of what your data looks like, you can use the conditional formatting options. You can even highlight errors if you want. Next, convert everything into seconds and then take logs. Make your life a little bit easier if you name your ranges. So you can reuse them again and again without having to go around the spreadsheet and trying to highlight the same cells. And if you use an array formula, you can see that they're in an array because the curly brackets are the side of it. You can start changing things much more easily. I can change that to 120 if I want. And input it with control shift return. There we go. Sorry, that needs to be time min control shift enter. There we go. Obviously, I've made that wrong there. I can do it easily. So once you've done all that, you can then use this slope command that gets you the actual slope of it so you don't need to copy and paste it off a graph. So at some point, you can actually do this entire equation, entire process without actually building a graph or trying to format it nicely at all. You can just get it straight from here. So hopefully that method made some sense. Go through and give it a shot yourself. There's the data values. You should be able to copy them down the screen if you want. Then I will see you at the next video. We'll cover some simulation and then probably the lecture where we'll move on to the next genetics topic.