All language subtitles for 02 - Measure maximums, minimums, and other data characteristics

- [Instructor] When you analyze a set of data,

you will often want to identify the smallest

and largest values, and also where you have cutoffs

within your data set.

In this movie, I will show you how to find the minimum

and maximum values,

as well as determine measures called quartiles.

My sample file is 01 02 minimums,

and you can find that in the chapter one folder

of the exercise files collection.

In this workbook, I have a set of order values

and they run from cell A2 all the way down,

scrolling to A40.

And I have them sorted in order so it will make it easier

to check the results of our formulas.

If I want to use a formula to find the minimum value

in my data set, then I can go to cell D2 in this case,

type in equal sign, and the function is min, M I N.

And my data range is A2 through A40.

So I'll type that in.

Right parenthesis to close out and Enter.

And I see that the minimum value is 1,684, which is correct.

And it corresponds to the first value in the data set in A2,

which again, I sorted in order from smallest to largest.

Finding the maximum value is also pretty straightforward.

So I'll click in cell D4, equal,

and we used min before, we'll use max now.

Left parenthesis, A2 colon A40.

Right parenthesis then Enter.

And the largest value is 9,932.

And if I scroll down to A40, hopefully I will see 9,932

And I do.

Then I'll go back up and we can go to cell D6

and create a formula to measure the quartile.

A quartile is a measure that shows where the 25, 50,

and 75% cutoffs are in your data set.

Rather than try to just explain it with words,

I'll go ahead and create the formula

and we can look at the result together.

So I'll type in equal sign in D6,

and I'll start typing my function, Q U A R T.

And we have quartile.

We have two versions:

quartile.exc which is exclusive,

and quartile.inc which is inclusive.

The difference between the two

is that exclusive only lets you identify quartile one,

two, and three, whereas quartile inclusive

also lets you identify the minimum and maximum value

using the quartile function.

So which one you use doesn't really make any difference

'cause we're only looking at the middle three,

and we're using min and max for our minimum and maximum.

So I'll just go ahead and use exclusive

which is the one that's highlighted now.

And my array is A2 through A40,

but I need to add another argument, so I'll type a comma.

And we need to identify the quartile.

In this case, we're looking at the 25th percentile.

So I'll press Tab.

I could've also typed in the number one,

right parenthesis, and Enter, and I get 4,293.

And if I look in the data list over on the left,

I see that value occurs in cell A11.

I have 10 values including 4,293 in the data set.

So that means that I have about 40 values

and quartile number one ends at value number 10

when the data list is sorted in order

from smallest to largest.

Now let's do the same thing with quartile two,

and I will change the highlighting of cell A11

so we remember where quartile one ends up.

So I'll go to cell D8, type in equal sign and quartile.

We're using exclusive, and then A2 through A40, comma,

and we'll do number two, which is the median value,

and right parenthesis and Enter, and we get 6,062, so 6,062.

So I'll scroll down.

There's a value in 21, or A21, right here.

And so I have, dragging to count,

I have 10 values in that quartile.

That makes sense.

So I will change the highlighting there

and go back up and do quartile three in cell D10.

So equal sign, quartile, exclusive, A2 though A40,

then we'll do the third quartile.

So I'll type number three and Enter, 7,491.

So I'll scroll down and then there is 7,491.

So I have 1, 2, 3, 10 values there.

So I will click cell A31, change the highlight,

and go down to the bottom.

And from 32 to 40, it looks like I have nine values there.

I do.

So we have 10 values in each of the first three quartiles,

and then for the fourth one, we have only nine,

and that includes our maximum value of 9,932.

As you can see,

this type of analysis can give you great insights

into your data, the minimum, the maximum,

and how the data is arranged or spread out

between those two values.