**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 to this video

#### Reference

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 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 up