WEBVTT Kind: captions; language: en-us NOTE Treffsikkerhet: 87% (H?Y) 00:00:00.000 --> 00:00:08.000 In this video, I will show you how to use formulas in Excel. Formulas are very useful in Excel and 00:00:08.000 --> 00:00:16.200 you can do a lot of things. I'm going to do it here using the example of calculating Z scores. So 00:00:16.200 --> 00:00:25.200 here I have copied some numbers from our dataset, to a fresh sheet in Excel. And if I click on this 00:00:25.200 --> 00:00:29.700 number here, you will see it appearing up here. NOTE Treffsikkerhet: 86% (H?Y) 00:00:29.700 --> 00:00:37.100 The contents of this cell are in here and this cell is also highlighting its coordinates. So it's 00:00:37.100 --> 00:00:44.150 column A and row two. By click here. That's column A row 3. NOTE Treffsikkerhet: 82% (H?Y) 00:00:44.150 --> 00:00:53.000 So the first thing you need to know is that I can add a formula to any cell by starting with the 00:00:53.000 --> 00:01:03.800 equal sign. So everything that starts with equal sign is a formula. So I can now type equals A2, + 00:01:03.800 --> 00:01:12.000 A3 and this instructs Excel to enter the sum of these two numbers into this cell. So I'm pressing 00:01:12.000 --> 00:01:13.950 enter and you see NOTE Treffsikkerhet: 75% (MEDIUM) 00:01:13.950 --> 00:01:20.000 98, which is the sum of 47 and 51. NOTE Treffsikkerhet: 86% (H?Y) 00:01:20.600 --> 00:01:27.600 Okay, we don't really need this number. So I'm going to click here and press delete so that it's 00:01:27.600 --> 00:01:29.350 deleted. NOTE Treffsikkerhet: 76% (H?Y) 00:01:29.350 --> 00:01:38.700 An easier way to add or to include cells in any formula is by clicking on them. So I didn't have to 00:01:38.700 --> 00:01:47.700 type A2 and A3. I could, let's enter the equal sign. I can click on A2 and it's 00:01:47.700 --> 00:01:57.000 automatically entered here. Then I type +, I click on A3 and I have the same formula and the same 00:01:57.000 --> 00:02:00.750 result and I'm going to delete it again. NOTE Treffsikkerhet: 83% (H?Y) 00:02:00.750 --> 00:02:06.000 The first of all, we're going to need to compute the mean of these numbers, which means we need 00:02:06.000 --> 00:02:18.700 their some. So what we could do is enter equals and then this plus this plus, and so on. This takes a 00:02:18.700 --> 00:02:24.600 long time and if we had hundreds of numbers, it would be very tiring. Indeed it wouldn't be worth 00:02:24.600 --> 00:02:31.550 the effort of using formulas. There is actually a much better way and that's through Excel functions. NOTE Treffsikkerhet: 78% (H?Y) 00:02:31.550 --> 00:02:35.900 So Excel has sum function. I can type sum NOTE Treffsikkerhet: 83% (H?Y) 00:02:35.900 --> 00:02:40.000 Type the three letters and open parenthesis. NOTE Treffsikkerhet: 80% (H?Y) 00:02:40.000 --> 00:02:49.450 And now I can tell Excel which numbers I want to sum and I can give Excel the whole range by holding 00:02:49.450 --> 00:02:54.100 the click. So I click and hold click and pull down. NOTE Treffsikkerhet: 86% (H?Y) 00:02:54.100 --> 00:03:04.750 So, I enter a whole range and now Excel displays A2 the colon here means all the way up to A15 00:03:04.750 --> 00:03:10.500 and you can see the whole range selected. So closing parenthesis. I'm typing the parentheses to 00:03:10.500 --> 00:03:16.350 close. Remember, it's very important. Every open parentheses must close. Otherwise, it's an error 00:03:16.350 --> 00:03:18.750 and you not going to get a result. NOTE Treffsikkerhet: 87% (H?Y) 00:03:18.750 --> 00:03:26.700 So, the sum of the range from A2 to A15. I'm pressing enter, and that's the sum. NOTE Treffsikkerhet: 91% (H?Y) 00:03:29.300 --> 00:03:36.600 Let me delete this and show you another way that you can select a range of numbers that are 00:03:36.600 --> 00:03:46.200 contiguous like this one. So, I'm typing equals, sum open parenthesis. I click on the first number 00:03:46.200 --> 00:03:51.700 not holding the click, and then going on to this little NOTE Treffsikkerhet: 80% (H?Y) 00:03:52.100 --> 00:03:57.600 square at the bottom right of the cell. NOTE Treffsikkerhet: 84% (H?Y) 00:03:57.600 --> 00:04:01.000 And I can pull it down. NOTE Treffsikkerhet: 91% (H?Y) 00:04:02.300 --> 00:04:06.149 By holding the click on it. NOTE Treffsikkerhet: 86% (H?Y) 00:04:06.149 --> 00:04:11.900 Which is the same thing as selecting the range before. So now all I have to do is close the 00:04:11.900 --> 00:04:20.500 parentheses and press enter. Okay, so I have the sum of all the numbers. The next thing I need to 00:04:20.500 --> 00:04:28.100 compute the mean, is how many they are. Excel has a function for it. I'm typing equals and then 00:04:28.100 --> 00:04:30.450 the word count. NOTE Treffsikkerhet: 85% (H?Y) 00:04:30.450 --> 00:04:40.000 Which will literally count them. Open parenthesis and select the same range by click hold all the 00:04:40.000 --> 00:04:51.100 way down. Close parenthesis. Enter. That's how many they are. The mean is equal to this NOTE Treffsikkerhet: 66% (MEDIUM) 00:04:51.200 --> 00:04:58.900 divided by this. And that's our mean. NOTE Treffsikkerhet: 90% (H?Y) 00:04:59.000 --> 00:05:05.800 Actually, I could have done it in an easier way because Excel gives me a function for it. So I could 00:05:05.800 --> 00:05:09.700 have type equals average. NOTE Treffsikkerhet: 90% (H?Y) 00:05:10.200 --> 00:05:17.550 Unfortunately, Excel uses the word average for the mean instead of mean. Open parentheses. NOTE Treffsikkerhet: 78% (H?Y) 00:05:17.550 --> 00:05:25.000 Select the range. Close parenthesis and of course they get the same result. NOTE Treffsikkerhet: 91% (H?Y) 00:05:28.100 --> 00:05:31.700 So this is our mean NOTE Treffsikkerhet: 86% (H?Y) 00:05:33.800 --> 00:05:42.300 and this is our mean too. This is our count and this is our sum. NOTE Treffsikkerhet: 91% (H?Y) 00:05:42.300 --> 00:05:48.900 I'm just typing here. No equal signs, just labels for us to know what is there. NOTE Treffsikkerhet: 91% (H?Y) 00:05:50.600 --> 00:05:59.200 Now I need the difference of each measurement from the mean. NOTE Treffsikkerhet: 85% (H?Y) 00:05:59.200 --> 00:06:03.000 So, what's the difference of these from the mean? NOTE Treffsikkerhet: 87% (H?Y) 00:06:03.700 --> 00:06:13.700 Start with an equal sign and I click on this minus the mean, so I'm going to click on the mean. NOTE Treffsikkerhet: 91% (H?Y) 00:06:13.900 --> 00:06:23.150 Enter. That's the difference. It's minus 10. So 47 is 10 below 57. That sounds right. NOTE Treffsikkerhet: 91% (H?Y) 00:06:23.150 --> 00:06:30.250 and I can do equal this minus NOTE Treffsikkerhet: 91% (H?Y) 00:06:30.250 --> 00:06:33.900 this and so on. NOTE Treffsikkerhet: 88% (H?Y) 00:06:34.100 --> 00:06:40.000 That's going to take a while. So you probably have guessed. I'm not going to do it like that. There 00:06:40.000 --> 00:06:41.800 is an easier way. NOTE Treffsikkerhet: 80% (H?Y) 00:06:41.800 --> 00:06:46.400 So I'm going to go back and delete this one. NOTE Treffsikkerhet: 91% (H?Y) 00:06:47.100 --> 00:06:50.600 One way to repeat a formula NOTE Treffsikkerhet: 77% (H?Y) 00:06:50.600 --> 00:06:57.500 is to drag it down. So if I click on this little square here and drag it down. NOTE Treffsikkerhet: 81% (H?Y) 00:06:57.500 --> 00:07:06.800 It repeats the formula by dragging the values. So I started with A2 minus A19, and then Excel 00:07:06.800 --> 00:07:14.500 automatically when I dragged it down, it turned two to three and nineteen to twenty. So, instead of 00:07:14.500 --> 00:07:25.100 subtracting this from this, if you go one down, it subtracts this from this, and if you go further 00:07:25.100 --> 00:07:27.700 down, we subtract this NOTE Treffsikkerhet: 87% (H?Y) 00:07:27.700 --> 00:07:35.700 from this. You see a problem here? The problem is that using these numbers. So going down on this 00:07:35.700 --> 00:07:45.500 range was appropriate. So Excel was right. Turn A2 to A3, to A4 to A5 to A6. This is all 00:07:45.500 --> 00:07:55.049 correct, but it shouldn't have turned A19 to A20 then 21, then 22 and so on. Because all of these 00:07:55.049 --> 00:07:57.600 need to be subtracted NOTE Treffsikkerhet: 91% (H?Y) 00:07:57.600 --> 00:08:07.900 from the same cell. The mean. We want this 19 to stay 19 and not to be increased when you pull down 00:08:07.900 --> 00:08:12.049 the range. So what can we do? NOTE Treffsikkerhet: 87% (H?Y) 00:08:12.049 --> 00:08:19.600 These are all wrong because they don't subtract the mean. We go back here and say, I want 19 to be 00:08:19.600 --> 00:08:25.800 fixed and using a dollar sign in Excel fixes this in the formula, so that it will not change. I'm 00:08:25.800 --> 00:08:27.600 pressing enter. NOTE Treffsikkerhet: 91% (H?Y) 00:08:27.600 --> 00:08:31.900 And now, pulling down the range. NOTE Treffsikkerhet: 91% (H?Y) 00:08:32.400 --> 00:08:37.650 Looks a little better if you look at each of these. NOTE Treffsikkerhet: 91% (H?Y) 00:08:37.650 --> 00:08:47.200 You see that what is on the left of the expression changes. So it's A10 11 12, but the right, the 00:08:47.200 --> 00:08:54.000 second term is always A19 because the dollar has prevented the 19 from increasing. So these are 00:08:54.000 --> 00:08:56.650 actually the differences NOTE Treffsikkerhet: 86% (H?Y) 00:08:56.650 --> 00:09:02.100 what we want. So it's our values minus the mean. NOTE Treffsikkerhet: 74% (MEDIUM) 00:09:04.800 --> 00:09:08.200 And these are our values. NOTE Treffsikkerhet: 81% (H?Y) 00:09:12.000 --> 00:09:19.650 Let's separate a header row, so we know what you have. So these are the differences from the mean. NOTE Treffsikkerhet: 91% (H?Y) 00:09:19.650 --> 00:09:26.400 What else do we need to do to calculate a standard deviation? Well, we need all these differences to 00:09:26.400 --> 00:09:33.550 be multiplied by themselves. Right? So what we want to create here is NOTE Treffsikkerhet: 87% (H?Y) 00:09:33.550 --> 00:09:44.050 these differences squared and how we do that equals this times itself. So the asterisk(*) is for 00:09:44.050 --> 00:09:51.100 multiplication and I click on the same cell again. So it's this number by itself. Enter. NOTE Treffsikkerhet: 76% (H?Y) 00:09:51.500 --> 00:09:59.200 If I pull down this range, then every number will be multiplied by itself. Or instead of pulling 00:09:59.200 --> 00:10:05.800 down. I can just double click on this little square here, which is the same as pulling to the end of 00:10:05.800 --> 00:10:10.400 the adjacent range because there are contiguous numbers here. NOTE Treffsikkerhet: 91% (H?Y) 00:10:10.400 --> 00:10:19.800 So these are these numbers multiplied by themselves. So now we have all the squared differences. 00:10:19.800 --> 00:10:24.900 What do we need to do with the squared differences to calculate the standard deviation? We need to 00:10:24.900 --> 00:10:34.500 add them up. So I'm going down here and typing equals sum open parenthesis and select the whole 00:10:34.500 --> 00:10:39.800 range by clicking and dragging and close the NOTE Treffsikkerhet: 79% (H?Y) 00:10:39.800 --> 00:10:45.400 parenthesis, enter. And that's the sum of the squared differences. NOTE Treffsikkerhet: 88% (H?Y) 00:10:45.800 --> 00:10:53.250 And what do I need to divide this by? Not the count, but the count minus one. NOTE Treffsikkerhet: 79% (H?Y) 00:10:53.250 --> 00:10:57.200 So, what you can do is NOTE Treffsikkerhet: 80% (H?Y) 00:10:58.400 --> 00:11:03.200 calculate, this minus one. NOTE Treffsikkerhet: 78% (H?Y) 00:11:04.900 --> 00:11:11.100 So, now I can divide this by this. NOTE Treffsikkerhet: 91% (H?Y) 00:11:12.200 --> 00:11:17.050 And I need one more thing to get the standard deviation. NOTE Treffsikkerhet: 91% (H?Y) 00:11:17.050 --> 00:11:23.700 I need the square root. So what is the square root? There is a function in Excel that's 00:11:23.700 --> 00:11:28.400 called SQRT open parentheses. NOTE Treffsikkerhet: 88% (H?Y) 00:11:28.400 --> 00:11:35.600 Close parenthesis. Enter. This is our standard deviation. And actually there is a function I could 00:11:35.600 --> 00:11:39.400 have used. It's called STDEV. NOTE Treffsikkerhet: 91% (H?Y) 00:11:42.900 --> 00:11:46.850 Which of course produces the same number. NOTE Treffsikkerhet: 78% (H?Y) 00:11:46.850 --> 00:11:54.350 So this is my n minus 1 and this is my standard deviation. NOTE Treffsikkerhet: 90% (H?Y) 00:11:54.350 --> 00:11:58.300 And this here is the variance. NOTE Treffsikkerhet: 91% (H?Y) 00:11:58.800 --> 00:12:02.700 This is also the standard deviation. NOTE Treffsikkerhet: 91% (H?Y) 00:12:03.300 --> 00:12:13.600 Okay. Now we're getting somewhere. What we need to compute this scores, is to divide the difference 00:12:13.600 --> 00:12:18.100 of each value from the mean by the standard deviation. NOTE Treffsikkerhet: 79% (H?Y) 00:12:18.600 --> 00:12:28.400 So, z-score is equal to this divided by this. NOTE Treffsikkerhet: 91% (H?Y) 00:12:28.400 --> 00:12:36.600 And because we want this to stay put, we're going to fix 20 with a dollar. NOTE Treffsikkerhet: 82% (H?Y) 00:12:39.100 --> 00:12:45.400 And I'm going to double click here, so that I get all of the Z scores. NOTE Treffsikkerhet: 89% (H?Y) 00:12:50.400 --> 00:12:56.850 What is the sum of all the Z scores? NOTE Treffsikkerhet: 83% (H?Y) 00:12:56.850 --> 00:13:03.800 Well, let's go back here to think about this a little bit. These are the distances of all the 00:13:03.800 --> 00:13:11.700 measurements from the mean. You already know what the sum of all deviations is, because that's how we 00:13:11.700 --> 00:13:20.400 define the mean. You know that the sum of all these is 0. So their average is 0. So if we 00:13:20.400 --> 00:13:27.300 divide them all by the same number, the sum of all these will be zero as well and their average and NOTE Treffsikkerhet: 86% (H?Y) 00:13:27.300 --> 00:13:30.700 indeed you can see it in Excel down here. NOTE Treffsikkerhet: 91% (H?Y) 00:13:31.400 --> 00:13:42.200 Let us finally get to the real important information, which is the percentiles. So what is the 00:13:42.200 --> 00:13:51.900 percentile that corresponds to a Z value, a z-score of - 0.83? Excel thankfully gives us a function to 00:13:51.900 --> 00:13:56.900 compute that and it's called NORM.S.DIST NOTE Treffsikkerhet: 86% (H?Y) 00:13:56.900 --> 00:14:09.100 So that's the standard normal distribution. Open parentheses. I want the 00:14:09.100 --> 00:14:11.900 percentile for this one. NOTE Treffsikkerhet: 89% (H?Y) 00:14:13.700 --> 00:14:23.900 If you have set your PC to Norwegian, then you'll need a semicolon here. If your computer is set to 00:14:23.900 --> 00:14:30.700 English you will need a comma here. To know which one you need to enter a second parameter. You 00:14:30.700 --> 00:14:39.400 should look here in the example. So when you type a function in Excel, Excel automatically pops up 00:14:39.400 --> 00:14:43.550 information about this function, in here it tells you that this function requires NOTE Treffsikkerhet: 77% (H?Y) 00:14:43.550 --> 00:14:52.000 two parameters. A Z score and value for the cumulative parameter that can be true or false. NOTE Treffsikkerhet: 89% (H?Y) 00:14:52.000 --> 00:14:59.200 In the cumulative, is the percentile and the false. Well, actually, it's the proportion and the 00:14:59.200 --> 00:15:04.500 false is the probability density function, which you don't need. And you see in here, it displays a 00:15:04.500 --> 00:15:11.400 semi-coma. If your Excel displays a comma here, just type a comma instead. So we want the cumulative 00:15:11.400 --> 00:15:16.050 here. So I double clicked on true or I could have just type NOTE Treffsikkerhet: 82% (H?Y) 00:15:16.050 --> 00:15:26.100 TRUE, close parenthesis and Excel doesn't care if you type capital or lower case letters. I have 00:15:26.100 --> 00:15:32.900 been using upper case letters here for the formulas because they look better as formulas. They look 00:15:32.900 --> 00:15:39.000 more like what Excel provides but it makes no difference. You could have just type norm as this with 00:15:39.000 --> 00:15:44.200 lowercase letters and Excel would do the same. So I'm going to press enter here. NOTE Treffsikkerhet: 82% (H?Y) 00:15:44.600 --> 00:15:52.650 And why I've got a zero here is because the number is rounded. I have selected no decimal places. 00:15:52.650 --> 00:15:59.800 So you have to be careful about your formats in your Excel. Look up here. NOTE Treffsikkerhet: 86% (H?Y) 00:16:00.100 --> 00:16:04.150 For some cells it might say General. NOTE Treffsikkerhet: 91% (H?Y) 00:16:04.150 --> 00:16:12.000 For others it might say number or other things and you should set this to what you actually have. 00:16:12.000 --> 00:16:19.200 Now we already had number and this means that this column happens to have no decimal places 00:16:19.200 --> 00:16:23.800 selected. So you need to show decimals. NOTE Treffsikkerhet: 90% (H?Y) 00:16:23.800 --> 00:16:31.700 I'm clicking on this one to add more decimals to the display. I could add more. I don't need those 00:16:31.700 --> 00:16:40.100 because we want percentiles, which is this times a hundred. So now I have two decimals here. NOTE Treffsikkerhet: 81% (H?Y) 00:16:40.400 --> 00:16:49.849 See, all these are said to be. Well, this was said to be number and I could add more or have fewer 00:16:49.849 --> 00:16:57.700 decimal places. This one said to be General. Excel decided to make it General because there's no 00:16:57.700 --> 00:17:04.200 decimal point. So, these don't need to be numbers. If I were to set this to be a number, it would 00:17:04.200 --> 00:17:09.849 display decimal places and I could not display them because they are NOTE Treffsikkerhet: 65% (MEDIUM) 00:17:09.849 --> 00:17:12.300 integers in this case and I don't need them. NOTE Treffsikkerhet: 88% (H?Y) 00:17:12.300 --> 00:17:19.500 So you have to be careful to distinguish between what is the number and what you looking at by 00:17:19.500 --> 00:17:21.800 checking your format. NOTE Treffsikkerhet: 91% (H?Y) 00:17:25.800 --> 00:17:34.500 So let's make these into actual percentiles by multiplying by a hundred. NOTE Treffsikkerhet: 91% (H?Y) 00:17:37.300 --> 00:17:40.199 And see now this is NOTE Treffsikkerhet: 91% (H?Y) 00:17:40.199 --> 00:17:44.100 general and in order to NOTE Treffsikkerhet: 88% (H?Y) 00:17:44.900 --> 00:17:51.500 remove the decimal places and use rounded percentiles. I had to choose a number and hide the 00:17:51.500 --> 00:17:58.850 decimal places. Double click, so I can get the percentiles. Then these are actually proportions. NOTE Treffsikkerhet: 73% (MEDIUM) 00:17:58.850 --> 00:18:01.500 And there are cumulative. NOTE Treffsikkerhet: 91% (H?Y) 00:18:01.500 --> 00:18:04.300 Proportions. NOTE Treffsikkerhet: 87% (H?Y) 00:18:07.400 --> 00:18:14.500 So in this way we have seen several important features of excel. We have seen how to use formulas 00:18:14.500 --> 00:18:21.700 and functions. And we have computed Z scores and percentiles for a set of measurements. NOTE Treffsikkerhet: 84% (H?Y) 00:18:22.000 --> 00:18:30.100 It is also very simple to compute scale scores based on these Z scores. NOTE Treffsikkerhet: 91% (H?Y) 00:18:31.300 --> 00:18:43.500 So to produce scale scores, all we have to do is multiply the Z score by our desired standard 00:18:43.500 --> 00:18:52.600 deviation and add our desired mean. So, I'm typing times 15 plus 100 enter. NOTE Treffsikkerhet: 90% (H?Y) 00:18:52.700 --> 00:18:56.650 And I'm going to click. NOTE Treffsikkerhet: 91% (H?Y) 00:18:56.650 --> 00:19:01.350 Double click here to extend the range all the way down. NOTE Treffsikkerhet: 90% (H?Y) 00:19:01.350 --> 00:19:09.400 Now, as you see, Excel has determined this to be a general format, but I want this to look like an 00:19:09.400 --> 00:19:21.600 integer. So I can turn it into a number and make the decimal places disappear. And I could very 00:19:21.600 --> 00:19:23.300 easily NOTE Treffsikkerhet: 85% (H?Y) 00:19:24.300 --> 00:19:31.500 produce a different scaling, for example a 10-3 NOTE Treffsikkerhet: 91% (H?Y) 00:19:33.200 --> 00:19:43.400 By doing equals, always have to go through the z-score there is no other way to rescale, because the 00:19:43.400 --> 00:19:49.900 z-score contains the information about the number of standard deviations. So multiply by the desired standard 00:19:49.900 --> 00:19:52.900 deviation, add the desired mean. NOTE Treffsikkerhet: 84% (H?Y) 00:19:54.100 --> 00:20:05.300 Turn this into a number. No decimals, and these are our scaled scores on a 10-3 scaling.