In this spreadsheet, and then copy and paste the data into a new spreadsheet and
then try and find the average number of Facebook friends. Hm, okay, link to
Udacian's Facebook friends, equals, I can't type anything. Okay, oh copy and
paste the data into your own spreadsheet. How do I do that? Now from Google
drive, looks like I have to sign in, I can go to create spreadsheet. Let's call
this the same thing that says here, this way I can reference back to this in the
future. Okay, so I have to Copy and Paste the data, edit, Copy's not there, I'm
going to copy it by brute force. Force command C. Maybe I'll just try copying
the data. Command c, command v, yay it worked. What's something else I could've
done? File, maybe download as Microsoft Excel. Hey maybe this'll work. It did! I
have an exact copy of the data and I can do the exact same thing in Microsoft
Excel. Well, I think I'll use Google Docs for now. Do-do-do-do-do. Oh no, I
accidentally clicked on it and it's gone, but here it is in my drive. From your
Google account, you can see all the options here at the top. And here's my
spreadsheet. Let's get to know this spreadsheet. This is cell A1, this is cell
A2. Each cell has a name just like you and me, except we usually don't have
numbers in our names. There's no comma or special characters in the name of the
cell, and it's always the letter first and then the number. What do you think
this cell is? Let's put a header on this data so we know what it is. Insert row
above. This is the header, and is not part of the data, it just tells us what
we're measuring. So that means, the data is from cell A2 to A28. Now I wonder
what cool stuff we could do with this data? How about data, sort, range. Oh,
data has header row. I almost said data has header row. Sort by number of
Facebook friends, indeed. What else could we do? What's this? That looks like
sigma sum. Here's a bunch of functions, let's add them. It automatically put the
formula here, we could've written that ourselves though and not been so lazy.
Actually, let's do some formulas. Let's calculate the average. Equals. Average
A2, because that's where our data starts, all the way to A28. Cool. And then we
can see here, in the formula bar, what we just typed. Let's find the deviation
from the mean. There's no formula for that, you just have to intuitively know
what it is. Deviation, meaning how far something differs. From the mean, how far
each value differs from the mean. How do we find the difference? By subtracting.
And how do we start our formulas? With an equals. A2 minus, and now we always
want this value to be constant. So instead of just writing E1, we're going to
put a dollar sign in between the letter and the number. That'll keep it constant
when we copy and paste this for the other cells. So that makes sense, right? 0
minus 584, blah-blah-blah should be negative 584, blah-blah-blah. So now, there
are a few things we can do. We can go to this corner here where there's a plus
sign, then drag it down, and then we get each deviation from the mean. In fact,
if we click on it, we see in the formula bar this is A10 here minus the mean,
which is E1. Another thing we could do is click on this cell, push command c,
or, if you're on a PC, Ctrl+C, go to cell A2, push command-down, go to cell B28,
push command-shift-up. And then that area is highlighted. And then push command
v. What's the sum of the deviations? Equals, sum B2:B28, it's like 0. It should
always be about 0. Let's name this column, Deviations. Xi minus x-bar, and x-bar
is the mean. Okay, let's do squared deviations. That's pretty self explanatory,
it's just the deviation squared, equals B2 squared. Now again, command c, go to
cell B2, command down. Go to cell C28, command shift-up, command v. I'm telling
you, if you get used to those keyboard shortcuts, you'll be able to do things
way fast, especially if you have a list of 500 data values. It might be easier
for short data sets to just drag this down, but for large data sets, these
keyboard shortcuts will make your life a lot easier. So now we have our squared
deviations. Let's find the sum of squares. Equals, sum, C2;C28. Error, I must
have typed something wrong. Let's look and see what I did. Oh, there's a comma
right there, let's delete it. Now we get the sum of squares, or SS. Let's find
the variance. That's simply the sum of squares divided by n, equals C30 divided
by, and it looks like there are 27 values. This goes until C28, but there's a
header here. So it's not using one of the cells. So there are only 27 values.
Cool. So this is the variance. Should probably write this down, just so you
remember. Oh, and look, it says all changes saved in drive. Every change you
make is automatically saved in drive, that is so awesome. I love Google Docs.
Notice that for variance, we divided by n, but if we were treating this as a
sample, we would divide by n minus 1. Okay, let's find the standard deviation,
equals square root of C31. So here is the standard deviation. Which remember,
it's represented by that Greek symbol Sigma. What if someone had a lot of
Facebook friends? Like 100,000. How would that change our mean standard
deviation and variant? Let's say that instead of having 408 Facebook friends, he
has 100,000. Ready for this? Whoa! Everything changed automatically. The
deviations are huge, because the mean is huge, and the square deviations are
huge, and everything's huge. That's so cool. And then you don't have to do
anything different. What if everybody had zero Facebook friends? Then
everything's just zero. Are spreadsheets totally awesome or what?