All right? So here's a little help file
on dealing with missing values in our
mini survey data. Basically, the reason
that we have any concern that there are
missing values is that for each case for
which there's a missing value in any
analysis. That includes that case. It'll
be omitted, so things per se, like
multiple regression or something like
that you end up losing a lot of cases if
you have a few missing values, scatter
around a number of different variables,
so what I've done here is I've
highlighted the cases that are the cases
they had something missing with yellow
highlighting and I just did that by hand.
There was no magic formula. Although, I
could have made some sort of conditional
if statement sort of thing. That would
have done that, but I was feeling lazy.
And there weren't that many cases. What
I've gone ahead and done is I've
calculated the mean which for this
variable or something missing is
1.65217 for the
median and the mode. And just to kind of
think about these are all central
tendencies of this variable and a lot of
a cheap and dirty way to deal with
missing values is substitute in the
central tendency, and then for linear
source of variables often times the mean
would be a good choice. In this case, we
have kind of these ordered levels in
terms of our Likert scales, and in that
case. Sometimes, maybe the median might be
superior. One advantage of the mean is
that if you substitute the mean, it'll be
1.652, which is
obviously not one of the choices, and you
can clearly see which ones were
substituted. In this case, just for our
purposes here, I'm highlighting them by
hand the other thing I'm going to do is
I'm going to put an equation here that
for these cases where they are being
substituted instead of putting the value
there. I'm going to put equals to the
median here, and I'm gonna make the
29 stay in place there by
putting a dollar sign in front of it;
make it an absolute reference. And that
means that I can copy this and paste it
in each of these subsequent spots,
regardless of which row it's in,
it's always going to be grabbing the
value. from row 29 for that particular
column all right? So I can come here and
I can substitute that in and here when I
substitute it, we'll see it changes in
here. And so here, this one, this one, and
you can see these ones here.
These questions were how much he uses
different statistics software, probably
the best guess is actually the median
rather than the mean in that case
because only one person here used that.
So anyway, this is probably the dominant
category here if someone left it blank,
they probably haven't used it.
Let me see. Let's drag this over a little
bit, and I can also I can fill these
across. It'll still work, so I don't have
to just paste paste paste. I can do a
whole row of them like that can go in,
oops. I need to recopy, so I can copy any
of these, paste it in here, and it'll work
and it'll keep grabbing the observation
from the 29th cell. So I need to
copy those in there, okay. And so at the
end of this, I'm gonna have a data set
that is almost all values. Oh, something I
should say is that I calculated the mean
median and mode before I started making
these changes if you hadn't. You'd get a
circular reference warning. So instead,
what you want to do is copy and paste
the values here instead of the formulas
for the mean, median mode and that's what
I already did. And so that's why we see
that there. Let's see. Here's a couple
more that I hadn't highlighted, so I'll
copy this and I'll paste it there. You
can see I'm pasting the formatting at
the same time which is kind of
convenient boom. Okay, hold on, okay. And
then we have a case here computers at
home, awesome, one listed three or more.
I'm just gonna go ahead and change that
to a three. I know it could be more than
that but for our purposes it's, we
have low, medium and high,
more or less, okay. And I'll look along
here. See if there any more missing cases.
There are a couple so I'm going to go
ahead and copy formatting.
The rule to look for the value from the
29th row, from here. I'm gonna paste it
into the remaining items. Now this was
not very efficient, yeah? I wouldn't do
this if I had a lot of cases,
but for our purposes, I think this will
work and we could talk about how to
automate these sort of a steps later. So
now what I'm going to do is I'm going to
copy everything in this sheet like this,
copy. And then, I'm gonna just shoot empty.
No, it's not. I'm gonna go and add yet
another sheet. I'm gonna paste special, so
right click and then click paste special
words. And then, I'm gonna click on
formats, okay. And then, I'm gonna click on
paste special values, okay. And so, then I
got my highlighting, and I got my numbers
but I didn't get my formulas which I
appreciate. So now this data set here. I'm
gonna go ahead and clear it. Clear this
stuff off the bottom clear contents because
I don't need it anymore. And now I'm
ready to do some analysis on this data,
yeah. So this would be a good starting
point for that. So pretty much we're done
processing the data in terms of missing
cases and stuff, gonna get ready to start
doing the next step, which would be
constructing an index, which I'll make a
brief video about here in a second.