Paired t-test with Confidence Intervals in Excel 2016

♪ (music) ♪

In this tutorial you
will learn how to do

a paired samples t-tests,
in Excel 2016.

In this comparison we
will compare

the stats pre and post
scores for students.

So first I'll start by taking both
columns, highlighting them,

copying them, and pasting
them into a new sheet.

Now in order to do a paired
samples t-test

we have to have the
same number of data points,

in both columns.

So as you can see
here in row 11,

we have missing data.

We cannot conduct this
test with missing data,

so we will have to
begin by removing rows

for values where we have
a missing point.

To do this the easiest way,
we will sort the data,

because this will put the
missing values at the end.

So if we go to "data", "sort",
and "sort first by the pre-column",

you can see that now if
we scroll down to the bottom,

the missing data from the
pre value is at the bottom.

So we will highlight that row,
right-click, and delete.

Then we will repeat this
for the post.

I'll highlight both columns,
sort, and sort by post,

scrolling down again,
you can see

we have several missing
values here,

so I'll highlight all
of those rows, and delete them.

Now that we have the same
number of values in both columns,

we can conduct our test.

To do this we will use the
Data Analysis Tool Pack,

and we will under the menu,
find the t-test,

paired two sample for means,
and click "OK".

In this test, Excel subtracts
whatever you have as variable one,

minus variable two.

So in this case I'm going to
go ahead and select

my post column as variable one,
and my pre column as variable two.

You can do either order just be
sure you are consistent

throughout your test.

Since I have labels here,
I will check the box,

because it says stat
pre and post,

and I selected them.

I'll leave my alpha level
At .05,

you don't have
to put anything

in the "hypothesized
mean difference" box,

because it will default to zero
which is what we want.

And then I will select
an output range,

that is near where
my data is,

and then click "OK".

You can see here that
the means for both of my groups,

are 6.90 and 5.59,
and when it was subtracted again,

Excel subtracted in the order
of my first variable, minus my second.

Since we had a larger
value first,

that led us to a
positive t-statistic of 4.17.

Then you can see here
it's given us our critical values,

and our p-values for our
one and two tailed tests.

Note that it says "E" here.

That means that this is
actually 8.76 times ten

to the negative fifth.

Or .00009 as our p value
for the two tailed test.

Look to the appropriate
p-value for your test.

If we want a confidence interval
on this test,

we will need to first
create a new column.

Remember a paired test
is the test of the differences,

so we will need to create
a difference column.

I will subtract in the same order
I did in my test.

So I will type in the first
box 'equals' the' post score',

minus the 'pre'.

And then I will copy this
all the way down,

by hovering over the corner
and double clicking.

Now that have my difference
scores for each sample,

I will highlight that column,
go to "Data Analysis",

and this time I will select
"Descriptive Statistics",

and click "OK".

My input range is the difference
column, I still have labels.

I'll select a new location
for my output range,

and then I am going to
request the summary statistics,

and the confidence level for mean.

And I'm going to connect
a 95% confidence interval,

so I will leave it at 95%,
and then click "OK".

You can see here now it's
given us the mean of the differences,

which will be equal to
what we would get

if we subtracted these
two means up here.

This confidence level is the
calculated margin of error.

It's already done all
of the work for us,

so we just have to calculate our lower
and upper confidence interval values.

So I'm going to label my
lower confidence interval,

and my upper confidence interval.

And to get these, we
just take our,

mean of the difference.

And to get the lower,

we will subtract our margin of error.

For the upper, we take
our mean again,

and we will add our
margin of error.

So you can see our
confidence interval

is between .68 and 1.93.

Since zero is not in
our confidence interval,

this indicates there is a
difference between

our pre and post scores.

Which you can see if we look
at the two tail results here,

that also indicates
this small p-value

that there is a significant difference
between our pre and post scores.