Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic 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

0:00
It's pretty cool how much information you can quickly calculate using a spreadsheet.

0:04
Previous generations had to spend a lot more time to answer questions that

0:07
spreadsheets let us answer in less than a second.

0:09
In this example spreadsheet, we're looking at a lot of different retail transactions.

0:13
And say we want to understand, what the average price of the items,

0:16
we are selling is.

0:17
You might do this to figure out if you have opportunities to change your

0:20
pricing strategies, for example.

0:22
To calculate the average price, we can use the average function which is similar to

0:26
the sum function we introduced in an earlier video.

0:29
In the average case, the syntax is very simple and similar to sum.

0:33
Let's scroll down to where we had the sum function.

0:40
And Instead of sum, we're gonna type out AVERAGE.

0:47
Then parentheticals, and then select all the values that we wanna sum.

0:57
So the average price transaction for this data set is $289.71.

1:03
The median function is basically the exact same as the average function,

1:07
except we're gonna be typing median instead of average.

1:10
Before using the median function,

1:12
let's note why sometimes an average is misleading.

1:15
Let's look at another data set to illustrate this.

1:18
We go over to the average median tab.

1:20
And this tab, we're looking at the total sales by month for a full year.

1:26
So, in January we had $500,000 worth of sales.

1:31
In August, $100,000 worth of sales, September at $90,000 worth of sales,

1:36
so forth and so on.

1:38
Let's say your boss asks you to calculate what the average monthly

1:40
sales were for the year.

1:42
January sales were much more than other months though.

1:45
In this case, the average might skew high and

1:47
not reflect the sales of most of the months.

1:50
The median is a better choice,

1:52
as it helps eliminate outline values that are extremely or low.

1:55
In this situation, you might answer your boss's question about what the average is,

1:59
and then also point out that you included median as well,

2:01
because it may be a better indicator for their underlying question.

2:05
Another option is to note that January was an outlier month, and

2:07
just remove that from your data set.

2:09
So here we've calculated the average for all the different calendar months.

2:15
And then below it, we've calculated the median.

2:20
The average is $135,833 and the median is $100,000.

2:24
So that's a significant difference.

2:26
What about the average is we remove the outlier month, as we've done here?

2:30
We removed January.

2:33
And now the average is $102,727, much closer to the median for

2:38
the entire data set.

2:40
Note that when I select a group of cells, I can get a handy snapshot of

2:44
a bunch of functions without having to write them down.

2:48
You can see it here in the bottom right of the screen.

2:52
We can see Sum, Average, Min, Max, Count, and Count Numbers.

2:58
Just something to keep in mind.

3:00
The sum is the total, the average is the average for

3:03
the cells that you've selected.

3:04
The min is the minimum value in the group of cells that you've selected.

3:08
The maximum is the maximum is the maximum

3:10
value in the group of cells you've selected.

3:13
Count is the number of cells you've selected, and

3:16
count numbers will only calculate numbers.

3:19
So if I go here and select all these and come back, you can see that count,

3:26
there's 14 cells that I've selected, but only 7 of them contain numerical values.

3:32
Now that you started using some functions,

3:34
let's see what else we can do with this simple example to gain further insights.
You need to sign up for Treehouse in order to download course files.
Sign up