All language subtitles for 01 - Calculate mean and median values

- [Presenter] Whenever you start to examine a new data set

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 files 01_01_Means,

and you can find it in the Chapter01 folder

of the Exercise Files collection.

The data set 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 and 1/2 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 & 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 six,

that's 1, 2, 3, 4, 5, 6, the median value is 28.

So if I go to cell D4 and type =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 Control + Z to undo my last change.

And we're back to our original data set.

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, then 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 data set

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 data set.

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 data set,

you can use mode mult.

So I'll go to cell D8, type an 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.