Calculate mean and median values - Microsoft Excel Tutorial

- [Instructor] Whenever you start to examine a new dataset, the first thing you should do is discover some basic facts about that data. In this movie, I will show you how to make several of those measures. My sample file is 01_01_Means, and you can find it in the chapter one folder of the Exercise Files collection. The dataset that I have in this workbook shows the number of days in transit for a number of shipments coming from overseas. So you can see the first shipment arrived in 23 days, the next in 38, and the rest of the data shows the other days that were measured. If you want to find the average or mean of that data, that would be the sum of all those values divided by the number of values. So if I go to cell D2, which is next to the mean label, and remember mean is the same as average, in D2, I can type equal and then the function we'll use is average, and I will select the data in the range A2 through A12 right parenthesis and enter, and we get a mean or average of 29.45 days. So just under 29.5 days on average. You can also calculate the median. The median is the middle value in a set of data that has been sorted into either ascending or descending order. For example, if I were to click cell A2, and then on the home tab of the ribbon, go to sort and filter and then click in this case sort smallest to largest, although I could do either largest to smallest or smallest to largest, then I get the values of 23, 27 all the way down to 38 I have, I'll just count here, 11 rows of data. So if I go down to the sixth, that's one, two, three, four, five, six. The median value is 28. So if I go to cell D4 and type equal, median and select A2 through A12, right parenthesis and enter, and I do in fact get 28. One way to examine your data is to compare the mean to the median, and in this case, they're very close. So that means that your data tends to be fairly well spread out. To give you an example of how the mean and median could change, let's say that we had a shipment that was just horrible. It took 75 days to arrive. So I'll go to cell A12 and change 38 to 75 and press enter. And you can see that the mean or average increased, but the median did not, and that's because the value of 28 is still in the middle of the sorted list. So I will press Ctrl Z to undo my last change, and we're back to our original dataset. One last thing about the median is that if you have an odd number of values, then the value in the middle of the sorted list is the median. If you have an even number of values, then Excel takes the average of the two that are in the middle. So in A2 through A12, we have 11 values. If I go to cell A13, and let's say I type in 39, then I can update the median formula so that it includes the new cell, A13, enter, and you see we get the median of 28.5, which is the average of the two values that are now in the middle of the list. I'll click to release the selection and press control + Z to undo the data entry and also the formula change. The final type of measure that I'll describe here is the mode, and that is the most frequently occurring value or values. If I go to cell D6, which is next to Mode single, I'll type equal, and then I'll type mode, and you see that underneath I have Mode.Mult, which is for multiple values or single, S-N-G-L. I'll click single for now and press tab, and I will select the same data range as before, A2 through A12, right parenthesis and enter, and we get 28. And if I go over to the dataset, I can see that 28 occurs three times, 27 twice, and 29 twice, and everyone else occurs a single time. So 28 is in fact the mode. If I were to change the value at the bottom from 38 to 27 and press enter, then you can see that the mode has changed to 27. And even though 27 and 28 occur the same number of times, 27 occurs first in the dataset. So that means that Excel will select it instead of going with the larger value. That's the rule it uses, the first value. If you do want to find multiple values that occur most frequently. In other words they're tied within a dataset, you can use Mode.Mult. So I'll go to cell D8, type in equal sign, mode M-U-L-T, and note that I still have my change from before. So I have 27 instead of 38. Select cells, A2 through A12, right parenthesis and enter, and we get 27 and 28. In the past, you would've had to identify the number of modes that you wanted to display using Mode.mult. However, in recent versions of Excel, there is the spill behavior, and that is what occurred here. Even though I typed the formula into cell D8, when I pressed enter because there were multiple values available, then it applied the same formula and spilled the result from cell D8 to D9. If I were to change the value in A12 from 27 back to 38, then you can see that the mode is still 28, and you can see that there is only one value that occurs most frequently in the data list. So as you can see, there are a lot of ways to get information about your data, whether the average, the middle value or median, or the most frequently occurring value or values. You can learn a lot in just a few minutes.