Welcome to Highline BI348 class video number 21.
If you want to download this file, BI348 Chapter 2 start,
or the finished file, click on the link below the video.
Hey, we want to talk about mean, median, and mode.
We have definitely looked at mean, median, mode
in our pre-req classes but it's worth noting what mean, median,
and mode are.
They are averages.
They are a way to go from lots of data points
into a single number that will represent a typical value.
It is also a measure of central tendency.
So mean, that simply means to add them up
and divide by the count.
And we use the Average function.
Now I'm going to do two examples.
Housing prices, Control-Shift-Down Arrow,
Control-Backspace, Tab, Tab.
We have a mean, that's our typical value.
The measure of central Tennessee $392,000.
So if these housing prices are from a particular neighborhood,
the Realtor could say the average price
in this neighborhood is 392,000.
Now here's some accounting salaries
from a particular firm.
Equals average and we'll calculate the mean.
Control-Shift-Down Arrow, Control-Backspace, and Enter.
So it looks like the average salary at this firm is 161,000.
Now as we mentioned in last video
with skew, if there are some extreme low or high values,
then the mean will either be pushed down,
if there's some really small values or pulled up,
if there are some really big values.
The median is the preferred average calculation when
there are some extreme values.
Now in our prerequisite class we learned
how to calculate median.
You actually have to sort the data set
and find positionally the one in the middle.
But no problem.
The Median function will do that for us.
So click in the top cell Control-Shift-Down Arrow,
Control-Backspace and Enter.
195,000.
That means if we sorted this list
and found the one in the middle, that's the median.
It is another way to get a typical value
or measure of central tendency.
Now look at this.
We already know that mean is much bigger than the median.
That means there has to be some really big values in this data
set that are pulling the mean up.
Over here, we can calculate median for our accounting firm
salary data.
Control-Backspace and Enter.
109,000.
Same thing here.
There's got to be some big values in this data set
that are pulling the mean up.
And sure enough if we look at an accounting firm like this
there are probably some partners that are making a lot of money.
And then some other people that are not making as much money.
So mean and median each has their place.
Median is good when there are extreme values.
The mode.
This function will be similar to what we
did last video with histograms.
It'll count.
Remember the tallest column have the biggest frequency?
Well the mode will look through all of the numbers
and find the one number that is repeated the most times.
Or the number that occurs most frequently.
Now mode is an old function.
That little yellow triangle means compatibility mode.
We want to use the equivalent mode.single here.
Now mode.single cannot deal with multiple modes.
But for this data set we'll try this.
If there are multiple modes Mode.single will only report
the first one it bumps into.
Control-Shift-Down Arrow, Control-Backspace and Enter.
N/A just means it's not available.
There are no duplicates in this data set.
="mode.single".
And I go and look at this data set.
Similarly, there are no repeats.
There's no duplicates.
No one value occurred more than one time.
If I were to force the issue just to show you,
then of course now it will report that value
because it found that it's listed twice
and every other value is listed once.
Control-Z.
Now I want to show you for mode- mean, median, and mode.
If your distribution is perfectly
symmetrical in its histogram or in its distribution,
then the mean, median, mode will all be the same value.
Now I want to actually go back to our data set.
I'm going to start a pivot table and then
put it onto our MMM sheet.
We go back to our sales data.
I want to click in a cell.
And I would like to count product because, guess what?
This is not a number.
This is a categorical variable.
And we cannot calculate the mean or the median because those
calculations require numbers or quantitative data.
So for categorical data we use the mode.
Which one occurs most frequently?
Now we can't use the Mode function
because the Mode function looks at numbers.
And I don't particularly want to extract a unique list
and use Countif's We could do that.
A pivot table is much easier.
I'm just going to do a pivot table.
So you ready?
Alt-N-V. And I actually want to tell this pivot table
to go onto an existing sheet location.
Well there's our MMM.
So I'm going to click on that sheet
and try and put it right here.
And click OK.
So we have our field list.
I'm going to drag Product down to rows.
Instantly we get a unique list.
Product down to values.
And instantly we have a count.
I'm going to close this.
Go up to Design Report Layout Show in Tabular.
Now I don't want to eye this.
I want to actually bring the one that occurs most frequently up
to the top.
So here's a great sorting trick for pivot tables.
I come over here.
And if I sort A to Z, it'll sort the actual product names.
But I want more sort options.
And I'm going to click.
Here's our dialog box.
I want descending.
I want the biggest one on top.
Product would just do the actual field names in the row area.
I want to see Count of Product.
Click OK.
And so the one that occurs most frequently, Fun Fly.
Then Quad.
So for categorical data mode is the measure
that we use for central tendency.
Now I want to scroll down one more example here mode.mult.
Now here we can clearly see we have some duplicates, right?
There's a bunch of 6's and it looks like there's a bunch
of 5's.
So let's try this.
="mode And I'm going to do mult. Now the thing is,
remember it's supposed to be looking for multiple modes.
And if it is returning multiple values,
there's no way it can display it in a single cell.
When I hit Enter, I might think there is only one.
But really there's multiple modes in this data set.
So one way to tell before you enter it,
is to highlight and hit the f9 key to evaluate it.
Once you see that there are two values, that means six and five
are simultaneously the mode, that's called bimodal.
Once you know that there's two- I'm simply
going to click Escape-Delete.
And because it's returning multiple values,
we need to enter them simultaneously
into multiple cells, in this case two cells.
Now I know it's two, so I highlight two cells.
Mode.mult, highlight, close parenthesis.
And just like the frequency function
which returns an array of values and we enter simultaneously
into multiple cells, we have to use
the special keystroke for this array function
Control-Shift-Enter.
Now Control-Shift-Enter is us telling Excel to enter it
as an array function.
But notice up in the formula bar those curly brackets
are Excel telling you it understood
that it is an array function.
Now another cheap and easy way, if you don't want to have to f9
to figure out exactly how many cells,
is just highlight a bunch of cells ="mode"
and then highlight the data, close parentheses.
Control-Shift-Enter to enter it into a bunch of cells.
And immediately we see that there are two.
The N/A just means no there is no third or fourth or fifth.
All right.
In this video we talked about mean, median, and mode.
We looked at the mode.mult array function.
We also talked about mean, which is a great,
universally used typical value average calculation.
The median.
You use it when there are extreme values
and when you have categorical data.
The mode calculation to figure out the one that occurs
most frequently is the one we should use.
All right next video we'll talk about the geometric mean.
All right we'll see you next video