Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Learn how to use the AVERAGE and MEDIAN functions to compute the average and median values for a selection of spreadsheet cells.
Example Files
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up on this video
Reference
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
It's pretty cool how much information you
can quickly calculate using a spreadsheet.
0:00
Previous generations had to spend a lot
more time to answer questions that
0:04
spreadsheets let us answer
in less than a second.
0:07
In this example spreadsheet, we're looking
at a lot of different retail transactions.
0:09
And say we want to understand,
what the average price of the items
0:13
we are selling is.
0:16
You might do this to figure out if
you have opportunities to change your
0:17
pricing strategies, for example.
0:20
To calculate the average price, we can use
the average function which is similar to
0:22
the sum function we introduced
in an earlier video.
0:26
In the average case, the syntax is
very simple and similar to sum.
0:29
Let's scroll down to where
we had the sum function.
0:33
And Instead of sum,
we're gonna type out AVERAGE.
0:40
Then parentheticals, and then select
all the values that we wanna sum.
0:47
So the average price transaction for
this data set is $289.71.
0:57
The median function is basically
the exact same as the average function,
1:03
except we're gonna be typing
median instead of average.
1:07
Before using the median function,
1:10
let's note why sometimes
an average is misleading.
1:12
Let's look at another data
set to illustrate this.
1:15
We go over to the average median tab.
1:18
And this tab, we're looking at the total
sales by month for a full year.
1:20
So, in January we had
$500,000 worth of sales.
1:26
In August, $100,000 worth of sales,
September at $90,000 worth of sales,
1:31
so forth and so on.
1:36
Let's say your boss asks you to
calculate what the average monthly
1:38
sales were for the year.
1:40
January sales were much more
than other months though.
1:42
In this case,
the average might skew high and
1:45
not reflect the sales
of most of the months.
1:47
The median is a better choice,
1:50
as it helps eliminate outline
values that are extremely or low.
1:52
In this situation, you might answer your
boss's question about what the average is,
1:55
and then also point out that
you included median as well,
1:59
because it may be a better indicator for
their underlying question.
2:01
Another option is to note that
January was an outlier month, and
2:05
just remove that from your data set.
2:07
So here we've calculated the average for
all the different calendar months.
2:09
And then below it,
we've calculated the median.
2:15
The average is $135,833 and
the median is $100,000.
2:20
So that's a significant difference.
2:24
What about the average is we remove
the outlier month, as we've done here?
2:26
We removed January.
2:30
And now the average is $102,727,
much closer to the median for
2:33
the entire data set.
2:38
Note that when I select a group of cells,
I can get a handy snapshot of
2:40
a bunch of functions without
having to write them down.
2:44
You can see it here in
the bottom right of the screen.
2:48
We can see Sum, Average, Min,
Max, Count, and Count Numbers.
2:52
Just something to keep in mind.
2:58
The sum is the total,
the average is the average for
3:00
the cells that you've selected.
3:03
The min is the minimum value in
the group of cells that you've selected.
3:04
The maximum is the maximum
3:08
value in the group of
cells you've selected.
3:10
Count is the number of
cells you've selected, and
3:13
count numbers will only calculate numbers.
3:16
So if I go here and select all these and
come back, you can see that count,
3:19
there's 14 cells that I've selected, but
only 7 of them contain numerical values.
3:26
Now that you started using some functions,
3:32
let's see what else we can do with this
simple example to gain further insights.
3:34
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up