Hello and welcome to a video on collapsing variables in Excel. Today we're going to be talking
about how to take a variable and one that is continuously measured and collapse
it into one that is categorical. This is what we mean when we say collapsing of variable. So
first let's say we want to find the average of our column
dv and find only values that are
above the average and for those values we want to put a one and for any other values we want
to put a zero. So the first thing that we'll do is find the average of dv and remember in Excel
that is equals average and then you select your entire column of data.
So here we've got 73.25662 and you can see we're just repeating the same step
that we've done right here so that you can see what the end result is before we get there. So we've
got our mean of dv. Now we're going to create a variable and we're going to call it underscore. I'm
going to call it dv2 since I've already made d_dv.
And now what we'd like to know is if
this cell is greater
than this cell. And we actually want to put
dollar signs in front of f and five, because we want to make
this a constant. The value if that is true. Now remember we're
saying if c2 is greater than f5, we want a 1 to be there because that means
it's greater than the mean. Otherwise, and here I put a comma, and now it's our value if
false. We want a zero. Close parentheses and hit enter.
And you can see I've got a zero there in a double click, so that that populates the rest of the way down.
And you can see we've got our ones in zeros now. And here's that formula again,
just so you can look it over. But that is one way that we can collapse a variable
in Excel. But what if we have multiple categories?
We need to collapse from our variables. We can use a different formula for this.
And let's go over here. To this 2x2 contingency table that I've made, where
we have 0, 1, low and high. Now the 0s and 1s are going to be using the
d_d_v2 that we just made. And the lows and highs are from IV2.
So what we would like to know is what if IV2 is below
the mean and d_d_dv = 0? Where
does it equal? Where is 1, low and the other 1? Where is 1, high and the other
low? So basically I want to know. Is IV2 IV2,
dv? Okay,
let's see if I can say that again, so it makes more sense.
Where is IV2 below the mean? Where d_dv is also 0?
Where is IV2 below the mean? Where d_dv is 1?
Where is IV2 above the mean? Where d_dv is 0? And where is IV2
above the mean? Where d_dv is equal to 1? Those are the questions that we're asking of this contingency
table. So we're going to have low. Hi.
Let me move this down a little. 0
and 1. Now in your data, this may be males and females. It may be yes, no. It may
be arrested or not arrested. It may be all kinds of other categories
that we could have here. But for this example, let's pretend that the zeros are males. The ones
are females. And we'll say that these IV2 is scores
on a test. Some sort of maybe like a-- what do these go up
to? We'll say there's scores on a math
test. And we want to know how many
males scored low on the math test, how many females scored low on the math test, how many males scored high on
the math test, and how many females scored high on the math test. That's what we would like to know. And we want that to widen up into
four categories. So we can use equals count
ifs. We've got multiple conditions that we're trying to meet remember. We've got two different variables
that we're looking at here. So we want to know, let's open up this parentheses. It's going to give us
our first criteria range here. So criteria range means what is the range
of values you're looking at where we want to set a criteria for. So in this case,
we're going to select de_db. I'm going
to scroll back up to the top so you can see. Comet criteria
one is that de_db is equal to, and we've decided males are zero. So zero.
Comet criteria range two, IV2, or our test scores. And I've already
found the mean of IV2 right
here in F3. When we
want to use a cell when using count ifs,
we have to do it in a very particular way. You put quotation marks around your
less than or equal to sign. In this case, we're going to say, because we're looking for low test scores.
We're going to say less than or equal to on the low one. Then we use the hamper sand symbol, and then
you can select your box where your
mean is in this case.
And we don't need to use the dollar signs to make this a constant this time because
we're using count ifs, and it looks at the entire column simultaneously. So we'll close our parenthesis here
and we'll hit enter and we've got 49. And we can see that's the same
number we had here. So we're doing good. Okay, so let's look at how many males
scored high on the test. And in this case, we're going to say equals count
ifs. See if I can type today. We're
going to select the same criteria ranges.
Scroll back up to the top so you can see comma
zero because we're still on males. comma criteria range two is IV two because
those are the math test scores that we're looking at.
comma and
we've already used equals for our less than so we're not going to use equals again. We'll
just say greater than ampersand F3 close parentheses.
So these are the males who scored higher than the average on the math test.
Now let's do the same thing, but for females so we use equals count hips. Our criteria
range is d_d_b for the first one. We're
still except this time, we're
going to say we want this equal to 1. Because 0 is for males and 1 is for females. Okay,
our second criteria range is IV2. And we want
to know
who scored low because that's the, that's the box we're in right now. So open quotation
marks less than or equal to quotation marks comma, oops, no
ampersand F3. Close parentheses.
Okay, and we've got 14 females who scored lower on this math test.
Okay, and now we're going to have another one. We want to know how many females scored high on the math test.
So again, we're going to select our criteria range one.
One for females, comma, criteria range
two. And for this one, we're looking for greater than. So open quotation, greater than symbol,
closed quotations, ampersand F3. Okay,
and we've got 68. So I know these are correct because these are the same numbers that I got up here. But
we can check these to make sure that we have correct answers and something that makes sense by
using some. So we're going to sum up our marginal totals. So you
can see we've got 88 and 82, but some these
marginal totals now. And I'm just
dragging this formula down. There we go. So if you click up in here,
you can see that this is the sum of 88 plus 82 equals 170. And
remember, we have labels so we don't count the first row. But if we scroll all the way to the bottom, we can
see that we have 170 points of data. We have 171 rows. But that first row is labels.
To be of 170 points of data in our data frame observations. And we have
170 observations here. So we know that we've collapsed our variables correctly from our two categories for
our four categories rather. All right. That should
be all you need to know to collapse variables in Excel. There are multiple different ways to do this.
So if you find another way that works better for you and gets you correct results, feel free
to use it. But that should be all you need. So have a great day and have
fun learning Excel.