Statistics in Education for Mere Mortals: Playing the ‘What if?’ Game with Excel


Hi Lloyd here again with some important
advice and the advice is this play the “What if?”
game with Excel to deepen your understanding
of Statistics I bring this up now because we’re at
that point of the course where we are just
beginning to learn some the most fundamental principles of statistics and I think every extra minute you spend
right now to really understand what’s going on
will be returned to you a hundredfold in the weeks, months and years to come the great thing
about a spreadsheet is that you can play the
“What if?” game that is what would happen if I change the raw
scores in different ways what will happen to the standard
deviation if I increase this number or decrease that number will the
standard deviation of z scores always really be one and so on so the point of this short video is not
to teach you anything new but instead to convince you
to take some time now to play with the ideas you’ve just
learned I suggest you do the following: first be sure you save your spreadsheet one
more time next let’s save it again but with a new name I’ll go to “save as…” and I suggest we simply save it with the same name but
with hyphen sandbox because in a sandbox we play we experiment we do things that
we don’t really expect to keep we’re just using it as a as a sandbox to
try things out Alright, let me save that. Now I’ll just demonstrate a few ideas and for example I wonder really if this z score like the sum of the z scores and the
standard deviation of the z scores will really change if I change
the scores So I’ll just go to the first
student’s score, and I’ll put in something a little crazy I’m going to put in ten thousand and I saw a lot of big changes here, the
standard deviation for example has really jumped I have a lot more variability in there
right now but notice that the sum of the z scores is still 0 and the standard deviation of the
z scores is still one now again if I keep putting in 10,000, just to watch how the standard deviation changes and now starting to go down and I put in
another 10,000 Whoa! What just happened?! Well, first of all notice that all the
scores are the same there is no variation therefore it makes
total sense that the standard deviation would be 0
well what happened over here with the z column what’s going on here?
well let’s take a look at the very first entry there in the z column and
there’s our formula up here and if I just do a click on it you will see what other
cells are involved, well C11, that’s a 0 and we are dividing by the standard
deviation which is in D26 and that’s a 0
also. Well you might know or remember that it is not allowed to divide by 0 hit that cancel there and you can see
the little exclamation mark and it gives me this explanation that
yes the formula or function used is dividing by 0 or empty cells dividing by 0 is not allowed it is referred to by mathematicians as
undefined I’ve always thought that was a bit of a cop
out by mathematicians they don’t have a definition for it so they
define it as undefined so in each of these cases we are dividing by 0 that is the
problem now if I come back over to, let’s just go to student J, I’m going to
change this to 10,001 everything is better now because we are no longer dividing by
0 of course the standard deviation is very very small that’s it I’ll stop there I was just trying to make an important
point and I hope you take my advice seriously to take again a
few minutes as you are learning these different
techniques these different concepts, these different
principles, and take full advantage of Excel to play the “What if?” game in order
to more fully understand what is going on so I hope you’ll play the “What if?” game
very often in the remaining time we have in the
course just remember to save your spreadsheet file first so that you have the values as I have
shown in the videos and then save it again with the word
like sandbox or some other word like that to let you
know that this is a file that you are allowed to play
with and you can make changes and you really are not worrying about what happens inside that file. Until next
time!

Leave a Reply

Your email address will not be published. Required fields are marked *