WEBVTT

00:00:00.000 --> 00:00:02.994 align:middle line:90%
[MUSIC PLAYING]

00:00:02.994 --> 00:00:16.490 align:middle line:90%


00:00:16.490 --> 00:00:18.170 align:middle line:84%
Hello, and welcome
to a brief tutorial

00:00:18.170 --> 00:00:22.310 align:middle line:84%
on how to calculate a
correlation using Excel.

00:00:22.310 --> 00:00:24.170 align:middle line:84%
So for this example,
the one right out

00:00:24.170 --> 00:00:26.270 align:middle line:84%
of the book, basically
what we wanted to look at

00:00:26.270 --> 00:00:28.910 align:middle line:84%
is the relationship between
an individual's level

00:00:28.910 --> 00:00:32.990 align:middle line:84%
of communication apprehension or
CA and their heart rate change

00:00:32.990 --> 00:00:35.250 align:middle line:90%
when giving a public speech.

00:00:35.250 --> 00:00:36.380 align:middle line:90%
So here's our basic data.

00:00:36.380 --> 00:00:38.546 align:middle line:84%
So we're going to do is
we're going to come in here,

00:00:38.546 --> 00:00:41.240 align:middle line:84%
and we're going to go up to
our new friend statistician.

00:00:41.240 --> 00:00:44.860 align:middle line:90%


00:00:44.860 --> 00:00:47.200 align:middle line:84%
So to calculate a correlation
what we're going to do

00:00:47.200 --> 00:00:52.314 align:middle line:84%
is we're going to come up
here to this one called Tools,

00:00:52.314 --> 00:00:54.730 align:middle line:84%
and we're going to click on
the one that says correlation.

00:00:54.730 --> 00:00:57.400 align:middle line:84%
And all we have to do is
select the two variables

00:00:57.400 --> 00:00:58.612 align:middle line:90%
that we want to correlate.

00:00:58.612 --> 00:01:00.820 align:middle line:84%
And notice here that I'm
selecting both communication

00:01:00.820 --> 00:01:02.448 align:middle line:84%
apprehension and
heart rate change,

00:01:02.448 --> 00:01:04.239 align:middle line:84%
and then I'm going to
click output results.

00:01:04.239 --> 00:01:06.572 align:middle line:84%
And then it's going to ask
me where I want to put these.

00:01:06.572 --> 00:01:09.642 align:middle line:84%
And obviously, don't want
to put those in a cell that

00:01:09.642 --> 00:01:10.600 align:middle line:90%
already has stuff that.

00:01:10.600 --> 00:01:12.860 align:middle line:84%
So we want to put it in an
empty cell like this one.

00:01:12.860 --> 00:01:15.740 align:middle line:84%
And then all you have
to do is click OK.

00:01:15.740 --> 00:01:18.504 align:middle line:84%
And then it's going to give
you your actual output.

00:01:18.504 --> 00:01:20.170 align:middle line:84%
Now again, one of the
things that you're

00:01:20.170 --> 00:01:23.080 align:middle line:84%
going to notice here is
that one of the limitations

00:01:23.080 --> 00:01:25.630 align:middle line:84%
is it doesn't give you
your actual p value.

00:01:25.630 --> 00:01:27.760 align:middle line:84%
So you will need to
go back and actually

00:01:27.760 --> 00:01:31.150 align:middle line:84%
compare based on the
degrees of freedom

00:01:31.150 --> 00:01:33.310 align:middle line:84%
and the calculated
r-squared value,

00:01:33.310 --> 00:01:36.707 align:middle line:84%
which you can see right
here, which is 0.91.

00:01:36.707 --> 00:01:38.290 align:middle line:84%
That's that communication
apprehension

00:01:38.290 --> 00:01:40.010 align:middle line:90%
with heart rate change.

00:01:40.010 --> 00:01:41.690 align:middle line:84%
And so that's how
you would do that.

00:01:41.690 --> 00:01:43.990 align:middle line:84%
So that's as simple
as it is to calculate

00:01:43.990 --> 00:01:46.610 align:middle line:90%
a Pearson's correlation.

00:01:46.610 --> 00:01:48.520 align:middle line:84%
Now we do want to
look at one that

00:01:48.520 --> 00:01:50.596 align:middle line:84%
gives us a little bit
of a bigger data set.

00:01:50.596 --> 00:01:51.970 align:middle line:84%
So we're going to
come down here,

00:01:51.970 --> 00:01:54.410 align:middle line:84%
and I'm going to
open up that larger

00:01:54.410 --> 00:01:57.307 align:middle line:90%
data that has no missing data.

00:01:57.307 --> 00:01:59.140 align:middle line:84%
And so I'm going to do
the exact same thing.

00:01:59.140 --> 00:02:00.970 align:middle line:84%
I'm going to come up
here to statistician.

00:02:00.970 --> 00:02:04.682 align:middle line:84%
I'm going to go to
Tools, Correlation,

00:02:04.682 --> 00:02:06.640 align:middle line:84%
and then I'm going to
find just a few variables

00:02:06.640 --> 00:02:07.681 align:middle line:90%
that I want to correlate.

00:02:07.681 --> 00:02:12.100 align:middle line:84%
So let's go ahead and correlate
communication apprehension,

00:02:12.100 --> 00:02:16.910 align:middle line:84%
assertiveness, responsiveness,
and willingness to communicate.

00:02:16.910 --> 00:02:19.494 align:middle line:84%
And so I need to
go way over here.

00:02:19.494 --> 00:02:21.160 align:middle line:84%
I'm going to have to
find a place to put

00:02:21.160 --> 00:02:22.850 align:middle line:90%
the output in just a second.

00:02:22.850 --> 00:02:25.290 align:middle line:84%
So I want to find this
big empty cell over here,

00:02:25.290 --> 00:02:26.920 align:middle line:90%
and I'm going to click OK.

00:02:26.920 --> 00:02:29.450 align:middle line:84%
And then it's going to go ahead
and paste that information.

00:02:29.450 --> 00:02:31.616 align:middle line:84%
So let's close out of that,
and I can come over here

00:02:31.616 --> 00:02:34.232 align:middle line:84%
and we can look at what
these results look like.

00:02:34.232 --> 00:02:35.690 align:middle line:84%
Now again, one of
the things you're

00:02:35.690 --> 00:02:38.740 align:middle line:84%
going to notice right off the
bat is some of the information

00:02:38.740 --> 00:02:39.830 align:middle line:90%
that you need is missing.

00:02:39.830 --> 00:02:43.432 align:middle line:84%
Like n isn't listed here,
but we know with this one

00:02:43.432 --> 00:02:44.890 align:middle line:84%
because it's choosing
all of these.

00:02:44.890 --> 00:02:46.510 align:middle line:84%
We can scroll down
to the very bottom

00:02:46.510 --> 00:02:48.610 align:middle line:84%
again and figure
out what our n was.

00:02:48.610 --> 00:02:53.530 align:middle line:84%
Our n in this case
would have been 549.

00:02:53.530 --> 00:02:55.460 align:middle line:84%
So that's how we
can determine that.

00:02:55.460 --> 00:02:56.980 align:middle line:84%
But again the
p-value, you will have

00:02:56.980 --> 00:03:01.372 align:middle line:84%
to compare the p-value to
the one in the textbook.

00:03:01.372 --> 00:03:02.330 align:middle line:90%
So let's look at these.

00:03:02.330 --> 00:03:03.880 align:middle line:84%
So we have communication
apprehension

00:03:03.880 --> 00:03:06.970 align:middle line:84%
correlated with assertiveness,
communication apprehension

00:03:06.970 --> 00:03:09.620 align:middle line:84%
correlated with
responsiveness, communication

00:03:09.620 --> 00:03:12.700 align:middle line:84%
apprehension correlated with
willingness to communicate.

00:03:12.700 --> 00:03:15.680 align:middle line:84%
Then you have the second column,
which is responsiveness with--

00:03:15.680 --> 00:03:16.760 align:middle line:90%
I'm sorry assertiveness.

00:03:16.760 --> 00:03:18.259 align:middle line:84%
Let me make that a
little bit bigger

00:03:18.259 --> 00:03:19.970 align:middle line:90%
so we can see these more easily.

00:03:19.970 --> 00:03:21.954 align:middle line:84%
So assertiveness
with responsiveness,

00:03:21.954 --> 00:03:23.870 align:middle line:84%
assertiveness with
willingness to communicate,

00:03:23.870 --> 00:03:28.171 align:middle line:84%
and lastly responsiveness with
willingness to communicate.

00:03:28.171 --> 00:03:29.920 align:middle line:84%
Now again, one of the
inherent limitations

00:03:29.920 --> 00:03:31.510 align:middle line:84%
here is that we
do not necessarily

00:03:31.510 --> 00:03:34.570 align:middle line:84%
know which of these is
statistically significant.

00:03:34.570 --> 00:03:36.340 align:middle line:84%
I can tell you from
looking at this

00:03:36.340 --> 00:03:40.780 align:middle line:84%
in previous statistical
software this one right here,

00:03:40.780 --> 00:03:44.960 align:middle line:84%
big CA with responsiveness is
not statistically significant.

00:03:44.960 --> 00:03:46.720 align:middle line:90%
The other ones all are.

00:03:46.720 --> 00:03:50.680 align:middle line:84%
So that is how you can
understand correlation matrices

00:03:50.680 --> 00:03:51.940 align:middle line:90%
using Excel.

00:03:51.940 --> 00:03:54.690 align:middle line:90%
[MUSIC PLAYING]

00:03:54.690 --> 00:04:06.130 align:middle line:90%