Adding Functions and Formulas8:06 with Michael Watson
Begin 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
You need to sign up for Treehouse in order to download course files.Sign up