Fall Semester 2002

Lab Three, Wednesday November 5, 2002

Date
Nov 5, 2002

Due today
Tutorial Two

Starting today
Excel Tutorial 3 (from the lab manual).

Due next time
  1. Homework Two will be collected.
  2. You need to show (and describe) your completed Tutorial Three.

Today's grading
You might receive questions of the following nature:

  1. How do you enter labels, values, formulas.
  2. Give examples of relative, absolute, and mixed references?
  3. When and how do you use AVERAGE, MIN, MAX, COUNT, MODE, MEDIAN?
  4. How do you insert a row?
  5. How do you move a range of cells?

In addition here's some help with the next assignment.

Standard scores.

What's the purpose of today's lab? To help with the homework assignment.

In what way? By guiding your calculations.
OK, let's see how that goes. Let's take problem two first.
It's shorter. It certainly is, so let's go for it.
What does it say? Here it is, in slow motion:
Problem Two

John is deciding whether to go to graduate school in business or law. He has taken nationally administered aptitude tests for both fields. John's scores along with the national norms are shown below.

National Norms
Field Mean Standard Deviation John's Score
Business 68 4.2 80.4
Law 85 3.6 89.8
Based solely on John's relative standing on these tests, which field should he enter?

Why does he need to go to graduate school? Well, let's see if we can help him.
Both tests are normally distributed. What does it mean?
It means that we have a means (a mathematical formula) with which we can calculate the percentile rank of any raw score. So we will do this with both scores for John and then advise him to go into the field in which his percentile rank is bigger?
Yes, because that means he has better chances in that field, doesn't it? More people behind him. I guess. I want to see the calculations though.
OK. Let me first ask you a question. Go right ahead.
How many normal distributions are there? I suppose an infinity, in general.
Indeed, since the formula has two parameters. But in our case they are known.
Yes, the mean is 68 and the standard deviation is 4.2 for one of the distributions (Business). And the mean is 85 and the standard deviation is 3.6 for the other distribution (the Law exam).
So how do you calculate the percentile rank of a value (raw score) x in a particular distribution? What score, and for which distribution?
Let's say, for the Business exam... I use the following formula 
=NORMDIST(80.4, 68, 4.2, 1)
What does it tell you? What percentage of people scored below 80.4 in the (normal) distribution of scores for that exam.
What kind of distribution is it? It's a normal distribution with a given mean (68) and a given standard deviation (4.2).
And what's the answer? It's 0.99 (that is, 99%).
Wow! John is quite an expert there... Let's see now how he did in the other exam.
Let's calculate: 
=NORMDIST(89.8, 85, 3.6, 1)
That's about 0.90 (or, 90%).
Still good, but not as good as in the other field. I have a question.
What is it? What's that 1 doing in the formula, at the end?
It helps us calculate the percentile rank, rather than the relative frequency of that score. I want to see an example.
OK, if you run this
=NORMDIST(86, 85, 3.6, 1)
 ... you get 0.60 (60%).
That's how many people scored 86 (or less) in this (85, 3.6) normal distribution. I see. That's just like before.
If you run this:
=NORMDIST(86, 85, 3.6, 0)
 I see you have replaced the 1 with a 0 (zero).
Yes, but what do you get? 0.10 (or 10%).
That's how many scored exactly 86 ... in this (85, 3.6) normal distribution.
So normal is a way of being... ... yes, and a very specific one.
A normal distribution remains normal regardless of what mean or standard deviation it has. Good, that means I can apply my formulas!
Why do we calculate standard scores? Good question.
They're easier to calculate and they give us the same information we've been looking at thus far. Can you show me that?
Apply the formula
Here are the z-scores, then:
(80.4 - 68) / 4.2

(89.8 - 85) / 3.6

The first one looks better. Indeed, that raw score is almost three standard deviations above the mean.
How big can a z-score get? Essentially it will be between -4 and 4.
Why? I'll let you think about that.
OK. One more question: what's due on Friday? Two more batches of quizzes in QuizSite.
Same rules as before? Yes, submit as many times as you want.
Good deal. Good luck.

Last updated: Nov 3, 2002 by Adrian German for A113