Preview
Video Player
00:00
00:00
Adding Functions and Formulas
8:06Begin to analyze our project spreadsheet by using functions like SUM, AVERAGE and MEDIAN to understand our data better.
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