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
Begin to analyze our project spreadsheet by using functions like SUM, AVERAGE and MEDIAN to understand our data better.
This video doesn't have any notes.
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
So we know that this spreadsheet has
column B which is really important
0:00
because it is the sum of
all the other columns.
0:05
So I'm actually going to change
the background color on this and
0:08
maybe even actually even
change the freeze pane.
0:12
What I want to do is start
to do some analysis here, so
0:20
I am going to say these are actuals,
this is the actual data,
0:25
and I am actually going
to merge all those cells.
0:30
Shrink that down.
0:41
And then what I'm gonna do is I'm going to
copy it so we can do some analysis on it.
0:44
Let's insert Columns to the right.
0:54
Insert 8 right.
0:58
Copy.
1:05
I wanna do one little divider row here,
column here, so insert one left.
1:09
I'm gonna make it smaller.
1:13
And I'm just gonna color it black so
1:16
that it's easy to recognize when I'm going
to a new section across the spreadsheet.
1:17
And this isn't actuals anymore,
let's do this % of total.
1:23
Okay, let's start inputting some formulas.
1:33
Equals C3,
we want it to be divided by the total
1:39
personal consumption expenditures for
that year.
1:43
If we copy this over, uh-oh.
1:51
It's not counting the percentage total of
personal consumption expenditure any more.
1:55
It's calculating durable household
equipment as a percent of motor
1:58
vehicle parts.
2:01
Which is not what we want so we're gonna
use a absolute reference to cell B3.
2:03
But we don't wanna do both column and
2:10
row absolute references we want
to just do colon and this is why.
2:13
We can copy this formula.
2:18
Left to right, and
it's accurate still, but
2:20
we can also copy this formula across
here and it's still accurate.
2:23
Click here, and it's updating
now motor vehicles in 1930 for
2:27
the total PCE or
Personal Consumption Expenditures in 1930.
2:32
Which is great,
because now I can take this formula and
2:37
copy it across all the different
categories we're interested in.
2:41
Don't want to go all the way down.
2:51
So here's a nice little shortcut
trick that you can use.
2:53
I'm going to jump over to this cell and
2:56
then because we know if we use
the command or control button and
2:59
skips to the next cell with something
in it or the edge of our spreadsheet.
3:02
I'm going to just put data in these cells,
so that when I copy this formula,
3:07
It stops at the 2015 row,
because now there's data to stop it there.
3:20
Those are a lot of numbers, let's make it
easier by formatting it as a percentage.
3:27
We've got the formats of
percent of total now,
3:34
let's look at what this
data is telling us.
3:38
In 1995, Americans spent 5% of their
total PCE on motor vehicle parts.
3:41
Compared to 4.26% in 1929.
3:49
Well that's interesting.
3:56
And look at this.
3:57
During World War II the percentage of PCE
3:59
that Americans spent on motor vehicles and
parts went down significantly.
4:03
But after the war it started
to bounce back again.
4:09
Let's calculate what
the average percentages are for
4:15
each of these categories across the years.
4:19
So =average, I'm going to sum.
4:21
So I held down Cmd+Shift there.
4:28
And it took me all the way to the top, and
because I have merged cells here in row
4:31
one, it selected everything that has a
column that's shared with this merged row.
4:36
That's not what we want.
4:42
So we wanna go down,
down while still holding shift and
4:43
left to get back to the original column.
4:49
Close the parentheses and there we are.
4:51
So we see that from 1929
to 2015 on average,
4:55
Americans spent 4.79% of their
PCE on motor vehicles and parts.
4:59
Earlier in the course,
5:06
we learned that average is
sometimes a misleading statistic.
5:07
Let's look at median.
5:13
Let's see how this fluctuates across
all the different categories.
5:21
The average and
median is actually sometimes very close.
5:29
And, For financial services, it seems
to be a little bit wider of a gap.
5:34
For transportation services,
it's very close.
5:41
So let's take a look at some
more of the columns and
5:48
see which ones are changing the most.
5:52
In 1929 Americans for example spent
21% of their income on food and
5:55
beverage for consumption off premise
of where it is purchased like
6:00
a grocery store for example.
6:05
And 4.39% of their gasoline and
other energy goods.
6:07
How about health care?
6:14
Health care, 2.9%, so
I'm actually gonna freeze this at the top.
6:15
So we can see 1929 the whole way.
6:21
Let's go down to 2015 and
see how much this has changed.
6:24
Americans are spending a lot of less as
a percent of their total expenditures
6:30
on food and beverage.
6:35
They're spending less on gasoline and
energy.
6:38
But they're spending a lot
more on health care.
6:45
Look at that.
6:47
16.8 % compared to 3% in 1929.
6:48
What other categories have gone up
since 1925, there's healthcare.
6:52
So I'm just gonna call this
out by highlighting that
6:58
cell in a yellow so I don't forget it.
7:03
What else, recreation.
7:06
It's gone up a bit,
I guess we're able to take more free time.
7:09
Financial services and
insurance has also gone up a lot and
7:14
same with food,
services and accommodation.
7:17
So I'm going to highlight those.
7:21
Housing and utilities we're also
spending more on in the US.
7:31
Clothing and footwear has gone way down.
7:37
So I'm going to highlight
7:41
that as a orange color just
7:46
to be different than the yellow.
7:51
As you can see, just with what you've
learned, you can build a very powerful
7:58
spreadsheet that can start to give
you some pretty interesting insights.
8:02
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