Sum, Count, Max, Min, and Range - Microsoft Excel Tutorial

- [Instructor] Imagine you're in a dark room and someone hands you an object. Immediately, you want to know what you're holding. You can feel its weight. Perhaps you start to feel the shape: round or rectangular, large, small, smooth, or rough. Sometimes, you can quickly guess what it is without even seeing it. Other times, you're clueless. You have no idea what you're holding. This is what it's like to be handed a dataset. At first, you have no idea what it is, what's inside that dataset. But, just as we can begin to understand the object in the dark by feeling it, we have descriptive tools and statistics that help us begin to understand our dataset. Let's look at some of these very basic descriptive tools that allow us to explore our new dataset. All right, so the first thing I want you to notice in the file that you have there is that in all of these cases, I've hidden the data from you. The data is in here. If we click here, you can see that this student is in Period 4, Student 1066, and they have a test score of 59, but we don't know what else is throughout that dataset. So again, it's kind of unknown. So what we want to do is we want to start to discover: How many data points are there? What's their sum? What's the biggest number, the smallest number? We're going to do that with a number of functions. So first, let's see how many data points are in our dataset. For this, we can use the COUNT function. Now, we can see whether or not there's a small number of test scores, test scores for a whole class, or test scores for an entire school. First thing we want to do is: How many students are there? So what I'm going to do is I'm going to click on this. I'm going to use the COUNT function and the number of students. I can choose this entire column B. And this shows me there are six students in this group of data. Now, the other thing is, I wonder how many test scores we have. Do we have a test score for every single student? So once again, equals, the COUNT function. And since I don't know how many data points are in here, I'm just going to click the entire column, C all the way to C, the entire column, and closed parentheses. And it looks like one of the students did not take the test. Next thing we want to do is we want to look at sum. So I wonder, "If we add up all the test scores for the five students that took it, what was their total amount of points that they got?" So once again, we can do the sum. We've seen that in other videos. We'll go ahead and use this again and we're going to sum up all the test scores. I don't know if that's too helpful, but in other cases, it might be a little bit more helpful. Next, we're going to use something called max and min. So what is the highest test score in this class? There's the MAX function. Click on this for the entire column. The highest test score in this group of five test scores is 75. The smallest test score, well, we have the MIN function. There you are right there. And once again, takes the entire column. Oh, telling me I made a mistake. And there we go. The minimum test score is 51, and it looks like these are just formatted a little bit differently. We can go ahead and fix that right there. The range, that's the difference between our biggest, or highest test score, and our lowest test score. So the way we do this is there's not a formula. We simply say, "Well, what's the difference between the largest test score when we subtract the smallest test score?" So for this particular set of data, we know that there are six students. Five took the test. Sum of all their test scores is 323; the largest test score is 75; smallest, 51; and the range is 24. Let's go ahead and do this for the other datasets. So we'll do this again, we'll count the students. We can do that here. So again, this is for a small class. What I'm going to do is I'll sum this up here as well. In this case, we have five students, all of them took the test. The sum of the test scores. Here we go. The maximum test score, in this case, it's right here. Our minimum test score, and then our range is going to be the highest score minus the smallest score. There we go. So little bit higher test scores in this particular class. These were small classes. Let's go ahead and do this for a larger class. How large is this class, I wonder? So once again, I'm going to count. How many students are there? 228. How many students took the exam? 221, so seven students didn't take the exam. We're going to add up all the test scores here. That's a really big number. Not sure that's much of a help, but again, we'll get to a point where that could be helpful. What's the max score? The highest score on this particular exam was a 100. The lowest test score on this particular exam was a 42. So this had a bigger range. 100 was the highest, 42 was the lowest. And there we go. Now, up to this point, we'll be looking at test scores. With financial data, we have a number of banks, so they have different branches; they have account numbers; and each account number, we think, has a balance. So the first thing we want to do is: I wonder how many accounts are in this particular set of bank data. So I do COUNT, and it looks like we have 1,012 accounts. I wonder if they all have a balance. Looks like they do. This could be helpful. In this case, when we sum them up, it allows us to see just how much money is in this particular bank, in this particular set of data for this bank. So I'm going to use... Once again, I'm going to use the SUM formula. I put this all together. Wow, there's $109 million in these 1,012 accounts. I wonder who has the biggest account. All right, the biggest account of these 1,000 accounts is... $12.4 million. The smallest account. Oh, it's negative. So somebody, in this case here, has a negative balance on their account, and that's why it shows up in the parenthesis. And so the range here, the difference between the biggest account and the smallest account, is well over 12.4 million. Let's do this for salaries, too, all right? Once again, they're giving us their weekly salary data. So how many employees do they have? So we're going to do this again. Employees, then we have our salaries, and it looks like one of the employees is no longer getting a salary or didn't when we got this set of data. I wonder what their weekly salaries all are together. So this company, how much do they pay out? In one week, we add all these together, and we'll turn this into a usable number here. Their weekly salaries that they pay out are $109,952. Of the 74 employees with a salary, the employee with the highest salary is getting $4,460 a week and the lowest-paid employee is getting $815 per week. And so the range of salaries, the biggest minus the smallest, is 3,645. Again, if we want to, we can generate some random test scores here. And how many do we want to do? We want to do 100 test scores. So going to go ahead and... We can go to, if we want, oh, let's go ahead and do this. Equals, there it is, RANDBETWEEN. And I'm going to make the exam scores for this test somewhere between 45 and 100. And by clicking this, this filled them all in by doing that double-click on the handle. Again, these are still alive. So what I want to do is I want to hit copy these, and you can either do a Control + C or you can use the menu here. I'm now going to paste them as values. There we go. So now I can... count up how many students I have, and we're expecting 100. I can count up how many scores I have. Again, I'm expecting 100. If I add all these up, my max for this entire column is 100, and my minimum for this entire column is 45, and the range is just going to be 100 minus the 45. And there we go. Hopefully now, by looking at these different functions: the COUNT function, SUM, MAX, MIN, and then ultimately being able to figure out your range, hopefully this has allowed you to see how we can begin to discover what's inside of our datasets.