In this video, learn how to complement that analysis by determining the spread of your data, in other words, what the minimum and the maximum values are and what the spread looks like between them.
- [Instructor] In the previous movie, I showed you how to measure the centrality of your data using means, or averages, medians, and modes. In this movie, I will show you how to complement that analysis by determining the spread of your data. In other words, what the minimum and maximum values are and using quartiles to describe what the spread looks like between them. My sample file is MinMax and that's an Excel workbook you can find in the chapter one folder of your exercise files collection. I have a data set that runs from cell A2 to A41, so I have 40 values.
And let's say that I want to find the minimum of those values. To do that, I can click in cell D2. Type an equal sign. And the function for finding the minimum of a data set is min, so just M-I-N. Type a left parentheses and then I'll select the values in A2 to A41 like this. I'll click cell A2 and then holding down the control key and the shift key, I'll press the down arrow.
And that takes me down to cell A41 and you can see that all of the data is selected. I'll click in the formula bar to the right of A41. Type a right parentheses and enter. And when I scroll up, I see the minimum value of 1684. Finding the maximum is very similar. So I'll just click cell D4, type an equal sign. And then max, M-A-X, left parentheses and this time, I'll just type in the reference. So A2 colon A41, right parentheses and enter.
And we get the max of 9932. Now we can look at quartiles. And as you can tell from the name, quartiles divide your data set into four segments. So you have the lowest quarter values, then the second quarter, then above the median for the entire data set, you have the third quartile and then the fourth quartile. So what we're looking for are values that form the boundaries between those quartiles.
There are two functions that you can use in Excel. And those inclusive and exclusive. The reason there are two separate functions in Excel is because there's no single agreed upon way to calculate quartile values. So I will show you both of them and you'll just have to figure out which one to use based on your own company or university's practices. I'll click in cell D7 and we'll start with inclusive. So in D7, I'll type an equal sign and then quartile.
And we're doing inclusive so I will press the down arrow to highlight quartile.inc, then a left parentheses. My array of values is as before, A2 colon A41. And I need to identify the quartile. That can go from zero to four. Zero's the minimum value, four is the max. And the first quartile cut off is 25%. Type one to enter it into the cell, right parentheses and enter. And we get 4310.25.
The second quartile value should be the median. So I'll type equal quartile.inc. Press tab, and A2 colon A41, comma, and then a two, right parentheses and enter. And 5924. And quartile number three will be the 75% cut off. In other words, there will be 75% of values below it and 25% above it. So type an equal sign in D11, quartile.inc, and then the array is A2 colon A41, a comma, and then three for the third quartile.
Right parentheses and I don't want to scroll to the bottom of the screen so I will press tab. And we get the value of 7370.25. And now let's do the exclusive calculations. So I'll click in cell G7. Type an equal sign and this time we'll have quartile.exc. Data is still in A2 colon A41, comma, and then one for the first quartile, right parentheses and enter. And we get 4298.75. And you can see that the values are different because there's a slightly different calculation method.
Now I'll go down to cell G9, equal, quartile.exc, and then A2 colon A41, comma, two, right parentheses and enter. And we get 5924.0 and you'll notice it's exactly the same in both methods. That's because the median of the data set doesn't change. Only the calculations for quartile one and quartile three do. To finish off the set, I'll go to cell G11, equal, quartile.exc, A2 colon A41, then a comma, then three for the third quartile and press tab.
And 7450.75. So again, we have a slightly different value for quartile number three. The reason that the inclusive and exclusive methods return different values for quartiles one and three depends on whether or not they include the median value in both halves of the data, if they're in odd number values or whether they exclude it from both halves. Depending upon how they do that particular calculation will change how they quartiles one and three values are calculated.
- 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.