 So this is the second video on Excel and linear regression. So by the end of the last video, we got up to this stage where we could extract a slope and an intercept using formulas, rather than just necessarily looking at what the trend line says. And now we're gonna get onto doing the errors associated with it as well. So to recap kind of the idea of errors, if you've looked at the preamble to this, you'll see a formula very similar to this. And this is telling you what the standard error of that slope is. And again, just like all the ones to do with this, it's a very complicated summing up dividing by stuff, taking away from averages and so on and doing some squares. It's really hard work to do for any more than a handful of data points. So we can just get a computer to do it. And a computer can put that formula in and calculate it for you instantly. But before doing that, I'm just gonna briefly cover what is sort of happening here, what you can do if you need to fudge it. So this is our least squares diagram. The blue circles represent real data points and the red line represents the best fit. And the best fit here means that these black lines, the distance and difference between the predicted value in red and the real value in blue, that distance is as low as it can be. You can fiddle around with the computer trying to sum this up and doing it manually or you can use some of the equations to do it in a simple way. But that is the lowest possible value. Now, imagine for a moment that maybe some of the data points are unreliable and we get rid of one. Well, the slope is gonna respond to that, it's going to maybe get a bit steeper because that's where the data is. The data is always gonna be at random. Your slope could be slightly higher or lower than what you estimated. But it doesn't need to be that point that it's wrong. It could be that point that's wrong. Which case the slope, maybe you can see, gets dragged down a little slower. In any case, it doesn't really matter because we just need to figure out what's the kind of the plus or the minus value of this. What's the steepest slope? What's the shallowest slope? And that's kind of what that equation does for us. And so we're now gonna have a look at Excel and how to implement that equation. So let's just switch back to that screen. And the formula we're gonna use is an array formula called line stats. And or line ST. Now, before doing this, I'm just gonna type in what it is. What's called an array formula. And an array formula works over multiple cells. A normal formula, we typed in slope, would just be in one cell. An array formula acts over multiple cells. So in order to enter it, I'm gonna have to highlight more than one cell. And in this case, for this specific example, we're gonna do a two by two box. So four cells in total, two by two. And leaving it there, not touching anything else. I'm gonna type in equals line ST. Or type in line and then tab to order to complete it. Now it's asking me for known Y values. And the known Y values are these ones here. But previously I gave them a name, so I just need to type in Y. So the good thing about using defined names, you can reuse them infinitely. Then comma, now it wants to do on Xs. So the exact same way around is the slope in the intercept, Ys first, Xs second. And then the next two questions are about options. Do you want to calculate the intercept normally or set it to zero? So for this we want to true. It's slightly counterintuitive in my opinion, but true means to calculate the intercept normally. Sometimes you can force the intercept through zero. It's probably not the best practice to do that. So let's come at that. And the final one is, do you want your additional regression statistics? And well, yeah, we do. The point of this is to get the plus and minus values of everything. So I'm gonna type in true there. It doesn't need to be in capital letters, but sometimes it feels nice. Now to enter that you do not press enter immediately. If you do, you're working, trying to figure out how to put the inputs there will be wasted. So to input an array formula, you need to press control, shift, hold those down and hit enter. So you do control, shift, return at the same time. If you try to get rid of a bit without that, it will give you a pop-up and not let you edit it. An array formula in Excel is effectively locked. So you have to select all of it to delete it. I'm gonna put these numbers back up. Now, what we can see here are two numbers on the top that we should already be able to see on screen. 1.0057, well that's up here on the trend line. It's 1.0057, it's the exact same as slope. So I'll quickly type in slope just to prove it. It's the exact same number. And the one on the right, 0.1857, well, 0.1857 is up here on the trend line as well. That is the intercept. The exact same number that you would get out of intercept. Type that in, 1857. The numbers underneath are the plus and minus values that you might want to use. So these are the standard errors of the slope and the intercept as determined by the linear regression formulas that tell you that. So these are really useful. Now, here's the thing you probably don't want all of these significant figures. So what I'm going to do is round them off by formatting. This is quite a good trick to make sure that your sheet is nice and clean. So on the home tab, this number has a format button that will just help you expand or contract the number of decimal places. And any errors or uncertainties, you just want one significant figure, that's it. And then the significant figures of the actual value need to match that. So my 0.04 here is one, two decimal places. So you want two decimal places for that. For here, my 0.1 is one decimal place. So you want, oops, I don't want a percentage. I want one significant figure, one decimal place for that. And that's it. So that's effectively it. Those are now the numbers that you'll want to quote. I'm going to very quickly show you a trick for formatting this. What I'm going to do is put these numbers back the way they are. That's just a formatting trick. The numbers behind the scenes are still always going to be, always use no matter how much you change this. So what I'm going to now do is type in equals round. And this rounds numbers off to a specified number of digits. So the number I want is going to be that intercept, not the, sorry, that's the slope. And comma, the number of digits I want is going to be two, two decimal places. So if I enter that, I get 0.01. And the really nice thing is it's actually rounded off. We now can't get any more numbers than that. And this is actually quite useful now. And I'm going to do is do the same thing by dragging it down there. So if I highlight something in Excel, click this little green box in the corner and drag down. It will drag down and all the reference cells will drag down with it. So I'm now looking at, instead of that box, I'm looking at that box. So I want 1.01 plus or minus 0.04. Well, what I'm going to do, let's see if I copy a character, is I'm going to type in equals that box there, ampersand character. And I'm going to put inside two quote marks, the plus and minus character. You can type that in if you've got off a numeric keyboard, it's two, four, one, hold down alt, two, four, one. I don't have one in front of me, unfortunately. So I have to copy and paste it from elsewhere. And I'm going to put that ampersand here. So now I've got 1.01 plus or minus 0.04. In fact, I can even just copy that formula and replace the cell with it. To copy that one. So what I'm now doing is rounding off the slope, putting a plus and minus character in the middle and rounding off the standard error. So this is how it should really be formatted when you're actually presenting it. So let me just put some formatting on there. So when you're presenting your number, it should look like that. It should have the right number of rounding off and it should have a plus and minus in it as well. And then I think about doing this in Excel and even doing this little trick here, is that if you re-record something in the lab and change the numbers, let's say that that becomes 0.05, this becomes 5.1, that comes 2.1, 3.05, 2.1. All of those are updating. This error here is now getting smaller. Let's make it even smaller, let's go 4.1. So now we're seeing all of this update in real time and live. So if you have to go back through your lab stuff and redo some numbers, Excel will automatically update it and it will save you time having to do these calculations again and again. So that will automatically update, that automatically updates. And that's what I mean by being quite powerful with the low barrier to entry. You don't need to know much about the code behind the scenes or anything like this. The most complicated thing we have to remember here is control shift return to enter this array formula. Because if you try to edit it, if we try to change this to false to force the intercepts through, it won't work, it will just shout at you. So you have to type in the changes and then control shift return and it changes. You can see I've changed this to false, which means there's now no intercept. Let's undo that. So hopefully this is really useful for you in the lab. Do review how to do this. Make sure you really well practice with it because it's really good when you're doing analytical, physical, any kind of experiments that involve linear regression. This is a super fast and simple way of getting the right answers.