"Build a Simple iPhone App with Swift 2.0" was retired on May 31, 2020. You are now viewing the recommended replacement.
Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Spreadsheet Basics!
You have completed Spreadsheet Basics!
Preview
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