Welcome to Excel 2013 Statistical Analysis
video number 32.
Hey, if you want to download this workbook and follow along,
click on the link below the video.
In this video, we want to talk about x and y,
Back in Chapter 3 we talked about covariance,
whole efficient of correlation, and x, y
scatters when it came to x and y.
In this video, we want to talk about the linear equation.
We want to take this data--
and we are going to calculate coefficient of correlation
in something called coefficient of determination.
And we're going to plot our scatter
plot to see if it looks like there's a linear relationship.
But once we think that there is, we're
going to go ahead and create this equation.
Now in math, you would have seen y,
or f of x equals mx plus b, the slope of the line--
for every one unit of x, how much does y move--
the slope times a particular x, plus the y-intercept.
The y-intercept can also be thought
of as, what's the value of this equation when x is 0?
Now in this textbook they use y hat equals
b sub 0-- that's the y-intercept-- plus
b sub 1-- that's the slope-- plus x.
Here's our formula to calculate the slope.
And here's our formula to calculate the y-intercept.
Now here's the formula we used back in Chapter 3
to calculate coefficient of correlation.
And it's similar.
We took the x minus the x bar times the y minus the y bar.
Multiplied all that out.
That's actually something we're going to use down here
in our slope formula.
Then we divide it by 1 minus 1, got covariance.
And then divided it by the standard deviation of both the
x and the y to get our r, or our coefficient of correlation.
Down here, for slope, we're not going to divide by all of that.
We're going to divide by the sum of x minus x bar squared.
And so we'll have this extra column here.
Then for our y-intercept we'll use y bar minus the newly
calculated slope times x bar.
And that will give us our y-intercept.
Now here's our two variables.
x is ad dollars spent per week and y is weekly sales.
Oftentimes you can think of the x as,
hey, which one comes first?
Which one is the predictor variable?
Hey ad dollars spent, hopefully that's
going to spur on sales, x, y.
Let's go ahead and calculate x bar first.
I'm going to click in cell B2 equals AVERAGE.
Highlight, and these will be relative cell references--
Control Enter.
So are x bar is 29,150.
And are y bar, 225,500.
I'm also going to calculate the n equals COUNT
and I'm going to count the numbers here.
So we have a sample size of 6.
Now we need to calculate our standard deviation for x and y.
We'll use our equals STDEV.S, this is sample data.
Control Enter, those are relative cell references
so we can copy over.
So standard deviation for the x is almost 12,000.
Standard deviation for the sales, the y, about $107,500.
Now before we do any of our calculations
to make our equation, let's go ahead
and plot this and visually see if there is a relationship.
We've got our headers at the top, x and y.
x is always to the left so the chart
will interpret it correctly.
Insert-- and there in Charts is our dropdown.
For sample data, you always will use the dotted or marker one.
All right, so just an initial look--
it looks like there's some sort of direct, meaning as x--
that's dollars spent on advertising-- increases,
it looks like the weekly sales are increasing.
Direct, as x increases, y increases.
Now I'm going to click on the title, type an equal sign,
shoot up to the Formula bar and then click on cell B5 Enter.
Go to the plus and immediately say I want axis titles.
That one's highlighted.
I type a equal sign.
I shoot up to the Formula bar, I click on y, Weekly Sales,
Enter.
I come down to the horizontal axis,
equal sign shoots me up here.
I click on cell B6, x ad dollars spent per week.
Enter.
There, we're looking good.
So our initial visual take on this
is it looks like there is some positive linear relationship.
As x increases, y increases.
So now we'll go ahead and calculate
coefficient of correlation and determination.
I'm going to scoot this down here.
Now again, the two formulas, the one
for coefficient of correlation and the one for our slope,
the top part is same.
We have to add up the deviations multiplied by each other.
That is, x minus x bar, that's a deviation.
And y minus y bar, that's a deviation.
Get them all, multiply them, copy them down, and add them.
So in this cell I'm going to say equals
open parentheses, the particular x minus the x bar.
And I'm in hit F4 to lock it, close parentheses,
times open parentheses.
I'll take the particular y minus the y bar.
And F4 to lock it, close parentheses, Control Enter.
Wow, that's a big number--
and copy it down.
Now we come to the bottom.
ALT equals and Enter.
So now we have the numerator for both the slope and coefficient
of correlation.
Now I'm going to calculate coefficient of correlation.
So I'm going to say equals--
hey, the sum of all the deviations multiplied
by each other, divided by-- and I
got to get the n minus 1, close parentheses.
And then I've got to multiply the two standard deviations,
so I'm going to use PRODUCT and simply highlight both of them,
close parentheses, and Enter.
So there it is, $0.95.
Control Shift Tilde or grave accent to wipe away
all that number formatting.
So 0.93.
Again, we saw visually that it looked like it was direct.
This tells us it is direct.
And that it is positive.
Now we can avoid having to do this longhand
by using either CORREL, or I'm going
to use the PEARSON function.
Pearson is the name the statistician
that invented this calculation.
And as we mentioned back in Chapter 3,
when it says array1 an array2, we'll put the y's in first,
because there's many functions related to x's and y's.
Most of them want y first, and then x.
But the ones that say array you can do it any in order.
But I always keep it simple and remember, y first then x.
And Enter, we get the same exact thing.
Now the coefficient of determination
is a goodness of fit for our line.
We simply have to take the r and square it.
So caret 2 and Enter.
And that's a pretty good number for our model.
We don't have to do it longhand, we can also use r square.
Known y's, this is one of the many functions that
will show the y's first in the screen tip,
so I'm going to highlight them and then highlight
the second one and Enter.
And there we go.
We got the same thing.
Now let's go ahead and calculate our-- and I'm going to move
this down here--
we'll calculate our slope.
So I'm going to come up here, equals--
and I've got to get the deviation again-- x
minus x bar, F4, close parentheses, caret 2.
Control, Enter, and I can copy this down.
Now I need to add these.
Over here, I see I have to add them.
So ALT equals.
And I'm going to scroll down a little bit here.
All right, so our slope is simply going to be,
hey, I'm going to take the sum of all
of the deviations multiplied and divide it
by the sum of the x deviation squared, Enter.
And there is our slope.
So it looks like our model will predict $8.43 up
for every $1 of ad revenue.
So about $8.50 of weekly sales for every $1 of ad.
Now we don't have to do it the long way.
We can use-- and they named this function smartly--
SLOPE.
Hey, there's the y, so I'm going to highlight the y's, comma,
and the x's.
I don't have to do any of this stuff here.
You've got to be kidding me.
Look at that!
Now the y-intercept, if we go ahead and use our formula,
we know y bar and x bar-- and we just calculated our slope--
equals-- I'll go up and get the y bar--
minus our slope times our x bar and Enter.
So it looks like minus 20,406.
So at x equals 0, we'll have weekly sales of a huge loss.
All right, and guess what?
There's an INTERCEPT function.
So equals INTERCEPT, and there's the known y's, comma,
and then known x's.
And Enter.
We get exactly the same thing.
Now the beautiful thing about this
is, now we have our model, right?
We can come down here and for our function--
and I like to use the f of x--
for f at ad dollars spent, 29,000.
What does our model predict?
Hey I'm going to say equals slope,
that's m times x plus rb, our y-intercept.
Or b sub 0, the y-intercept, plus the slope times the x.
Of course, either one will work just fine.
And there it is.
At 29,000-- and I'm going to use some number formatting here
for currency, so at $29,000 spent,
it looks like our model will predict $224,234, about,
for our weekly sales.
Now we want to go over and look at our second example, SI2.
Hey, our data set is Used Toyota Camry data.
Here are miles on the odometer.
And here's what the car sold for as a used car.
Now I've already plotted this.
We have miles, right?
Because miles should probably help predict price.
It looks like as the miles are increasing,
the price is going down.
So this is an inverse or negative relationship.
And that sort of makes sense, right?
The more miles on the car, the more wear, the lower
the price for the used car.
Now you can see right here, we plotted this chart back
in Chapter 3 and we saw how easy it is to add an equation.
But the equation on the chart is just a visual there.
If you want those numbers to use to help predict,
then you have to come over and make the calculations
in the cell.
Now we'll do our PEARSON coefficient
of correlation equals PEARSON.
I'm going to put the y's first, comma, and then the x's, Enter.
So it looks like there's a negative relationship.
And it looks like it's fairly strong, 0.76.
Our coefficient of determination,
our goodness of fit, we're simply
going to take the r we just calculated and square it.
And so 0.59.
That's not real strong, but it's OK.
For our model we can use it to predict.
Let's calculate our slope--
equals slope.
Oh there's the y's, comma, and the x's.
So the slope is going to be minus 0.08.
So really, we can interpret this as, for every 1 mile added
on the Camry we will lose 8 and 1/2 pennies in our sell price
for our used car.
x and y, the slope is always going
to be for every one unit of x, how much does y move?
Our intercept equals I-N, INTERCEPT.
They named it smartly.
We'll do our y's, comma, and our x's.
And so the y-intercept, it will cross at 17,000.
That means for our model, that if we drive it or push it off
the lot-- we just bought it, there's no miles at all--
it'll drop right down to 17,000.
All right, so now if we have 43,520--
and I actually drive a Toyota Camry,
and that is the odometer reading right now.
So I'm going to, drrr, drum roll and see what my Camry--
what the model thinks my Camry can be sold for.
Hey, I'm going to say equals x times the particular slope
plus the y-intercept.
Notice this one, it starts at a positive.
And as x increases, it keeps subtracting
this amount for every one mile.
So the current price 14,000.
I wouldn't pay 14,000 for my car.
But that's what our model, based on the data, says.
All right so in this video, we saw
how to take an x and y data set and calculate
the slope, the intercept, and then
use it in a linear equation to make a prediction.
All right, we'll see you next video.