Some of those facts could include the mean or average value, the median, which is the value that would appear in the middle of a list if it were sorted into order, and finally, the mode, which is the most common value.
- [Instructor] Whenever you start to examine a new dataset, the first thing you should do is discover some basic facts. Some of those facts could include the mean, also called the average value, the median, which is the value that would appear in the middle of the list if it were sorted in order, and finally the mode, which is the most common value. In this movie, I will show you how to do all three of those things in Excel. My sample file is MeansAndMedians, that's an Excel workbook that you can find in the Chapter One folder of the exercise files collection.
My data on this worksheet represents days in transit, so for example, you might be shipping items from Shenzhen, China, and those will spend a certain amount of time on the ocean, and with shipping schedules and weather you can never be sure how long that's going to be, so you can see that we have quite a few values. If you want to find the average, or the mean, then you can use the average function. I'll click in cell D2, and type an equal sign to start my formula, and then type AVERAGE, and then with the left parentheses which we either typed or inserted through autocomplete, select cells A2 through A12, type a right parentheses to close, enter, and you have the average, which is 29.45 repeating.
This is a representative average value, but you can see that it would be much more useful for data that's evenly distributed. So if you have two values, such as one and 99, which average 50, and two others, such as 48 and 92, which also average 50, the answer's clearly more meaningful for the second pair. A second way that you can measure what's called the centrality of your data is by finding the median, and the median is the value that would appear in the middle of a list that is sorted into either ascending or descending order.
So I'll click in cell D4, type an equal sign, and the median formula, or function, is as you would expect, MEDIAN, and the cell range is A2 through A12 as before. Type a right parentheses, and enter, and we see that the middle value is 28 days. So even though we have a low, it looks like a 27, and a high of 38, 28 would appear in the middle. To illustrate the point, I will select cells A2 through A12, and then on the Home tab I will sort from smallest to largest.
I could do it the other way as well. And I have 11 values, so if I count down from the top, one, two, three, four, five, six, which is in the middle, you see the middle is 28. And it is representative of the data in the set. I'll press Control-Z to undo the sort. Finally, you can look for the most common value, and that is called the mode. There are two different ways you can do that. You can look for a single value that is the most common, or if you suspect there might be ties, you can look for multiple values.
I've already clicked in cell D6, where I'll create my first formula, so type an equal sign, and then I'll type mode, and you can see that I can select from three functions. The one at the bottom, MODE, is the same as MODE.SNGL. It's deprecated, which means that it was from an earlier version of Excel, but it's been updated. Now I can either find a single value, or multiple values. In this case, I'll just type, or click, MODE.SNGL for single.
Press tab to accept it, and once again, select A2 through A12. Right parentheses, enter, and I see that the most common value is 28. And if I look over at the values and days in transit, I can see that 28 occurs three times in cells A5, A7, and A11, so that is in fact the most common. Well, let's say that we have multiple values. For example, if I change the last 28 to a 31, which doesn't occur anywhere else, first I change the mean and the median, and I've also changed the mode.
I have 29, which is the singular mode in cells A4 and A8, but I also have two 28s and two 27s, and the reason Excel returned 29 is that it is the first value that it encounters that occurs the most times. But now let's say that you suspect that there are several values that occur most frequently, in other words, they're tied. To detect those values, you can use MODE.MULT, or multiple.
Let's say that I suspect there are three values that occur the same amount of times most frequently within the data. To do that, I will select cells D6 through D8, type equal, and then MODE.MULT, again, that's for multiple, and I will select cells A2 through A12, type a right parentheses to close out. But because I want to enter this formula as an array formula, into multiple cells, I'll need to press Control + Shift + Enter.
So I'll hold down the Control and Shift keys and press enter and I get three values, 29, 28, and 27, and again, those results are based on order of appearance. So 29 appears in A4, 28 in A5, and the first time 27 appears is in A9. If I had only selected cells D6 and D7, then I would only get 29 and 28, and 27 would be left off. But let's see what happens if you do the same thing, and you select more cells than there are mode values.
I'll select cells D6 through D9, type an equal sign and it'll just repeat the formula I had before. MODE.MULT, A2 through A12, right parentheses, and again, Control + Shift + Enter, 'cause that's an array formula, and I get a similar result, 29, 28, 27, and N/A, which means Not Available. So in this case we had 29, 28, and 27 occurring the same number of times. There was no fourth value that occurred that number of times, so we got the N/A error for that result.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.