Statistical functions: AVERAGE, STDEV, MIN, and MAX - Microsoft Excel Tutorial

- [Instructor] Engineers get questions every day. Does the new production line perform better than the old one? Can we get rid of the expensive humidity control system? What should we do to minimize the number of defect solar panels? And many more. Oftentimes, answering such a question requires statistical analysis of production data. I can't even begin to scratch the surface of statistics theory but I will show you some relevant functions in Excel. Excel has many statistical functions and I'll show you where to find them and advise on how to choose. So let's take a look at our panel defects workbook and look at some of the statistical functions. So in this table, we have listed for machine A on a certain date and time, the number of panels per hour and the number of defect panels. By default, Excel already displays statistical information of cells that you select. So if, for example, I select a couple of cells here, Excel will show me the average, the count and the sum and I see many people watching that status bar and actually copying the image information from it. Don't do that. Use formulas. So how do you find the statistical functions? Let's move over to the tab called Basic Statistics because I listed a couple of them here. Suppose we want the average. On the Formulas tab of the ribbon, the functions are divided in various categories and if you can't find the category you need, click on the More Functions one and you'll see additional categories and the one that we're looking at today is the statistical category. So whereas this lists all of them, I find it's not very easy to navigate. So I usually click the insert function button, which you can also find to the left of the formula bar right here and then select the category from here. And not only gives me the same list, but it also allows me to scroll to the list and every time I select an item, it's going to give me a short explanation here of what the function is about. That really helps me to select the right function. So let's take the average function. I'll click OK and that causes the function wizard to open. And one thing I really like about the function wizard is that it helps me write the entire formula. For example, if I want to average cell C12 and cell C15, what that function wizard does for me is not only does it enter the function name and the opening and closing brackets, it also ensures that the argument separators are inserted at the right positions in my formula. So that is the average function, which I have also used here. Similarly, I've also used the min and the max function. These three are probably the most used statistical functions in Excel. Now, this is where things start to get slightly more complicated. Suppose I want to calculate the standard deviation. As you can see, if I type STD in a cell, Excel offers eight different variations of the standard deviation. Luckily, you don't need all eight of them. Usually, the first or the second one are what you need. If you wonder which one you need for your specific situation, let's have a look at both the STDEVP and the STDEVS functions. So if I press Tab here, and then hit Shift + F3 to open the function wizard, I can click on this help on this function link to open up the help for the STDEVP function. So here on the left, we have the STDEVP function and on the right, we have the STDEVS function. And the P stands for population, whereas the S stands for sample. So suppose your production line measures properties of every single solar panel that's being produced. That is where you need the STDEVP because you're calculating the standard deviation of the entire population. On the other hand, you might be sampling your solar panels. Suppose you're doing a destructive test, like I don't know, measuring the strength of the panel. You let them drop for a meter. That is where you need the STDEVS because you're only testing a sample of the panels. So here in cell C15, I've entered the STDEVP function because I know the panels per hour for each and every hour of my production. But a very important one, if you want to give a reliable answer to the question is machine A performing better than machine B? For example, look at these two averages. Machine A has an average number of defect panels per day of 4.05, whereas machine B has an average of 3.86. Now, is this a really different result? Statisticians have defined that if the chance that the difference between two numbers is less than 5%, they call that difference statistically significant. If we translate that to manager's language, if the number is less than 0.05, machine A is actually performing worse than machine B. So how does this T-test function work? I'll open the function wizard because that's an easy way to look at the function. So the function takes four arguments, and the first one, of course, is the number of defect panels of machine A and then the second one is the number of defect panels for machine B because those are the two averages that we want to compare. The third argument determines whether your data is one or two tailed. In this particular example, our data is two tailed. The last argument tells the T-test function whether you are comparing two samples which have equal variants or two samples which have unequal variance. I decided that we needed the number two here. So in the end result of this calculation is the fact that this T-test gives us a number that's smaller than 0.05 indicates that machine B is actually performing significantly better than machine A if it comes to the number of defects per day. My intent on this video was to get you started on using statistical functions in Excel. So now you know where to find them and I've shown you how the most important ones work.