0:05

Welcome to the fifth and last exercise of this online course.

Today, we're going to help the dean of the fictive University

of Claudidorf to see if the students of his classes are cheating or not.

His assumption is that if the grades of the students in the subject are normally

distributed, then students are not cheating.

However, if the grades are somehow not normally distributed,

something strange is happening, and he will take a closer look at it.

In order to do this, we will have a look at the grades of two different subjects

at this university, and after answering some easy questions, plot QQ plots

to find if the data in two different subjects are normally distributed.

0:57

In the first table that we see here, we have the grades, which go from one to ten,

and then the frequency of the grades in two subjects.

The first subject is Introduction to Analysis, we see 0 students have a 1,

3 students have a 2, other 3 students have a grade of 3 and so forth and so on.

Additionally, we have the frequency of the grades in Introduction to Probability.

So we will have 8 students with an 8,

5 students with a grade of 9 and so forth and so on.

1:27

Additionally, here we have a Student Rank and the grades ordered by this rank.

So 1 would correspond to the student with the poorest grade.

And if we go further down,

29 would correspond to the student with the highest grade.

So we have here the grades in each of the subjects ordered by a ranking.

Now taking this information, we can go and answer the questions.

2:03

The first question asked is to plot a histogram of the grades in every subject.

Additionally, we get here a hint, which asks us to use the command INSERT,

GRAPH and COLUMN graph to plot this histogram.

So what we need to do is to select the column of data, of each of the subjects

that I just showed, click on INSERT, GRAPH, COLUMN to plot a histogram.

2:46

Here, we see the histogram of the frequency of the grades

in the subject Introduction to Analysis.

Although we don't know much about the data,

we see that this distribution somehow could resemble a normal distribution.

3:01

If we scroll a little bit down, here we have the histogram for

the frequency of the grades in Introduction to Probability.

Again, although we don't know much about the data yet, we see that this

structure is far away of what we would expect in a normal distribution.

3:21

Now we are asked to interpret these two graphs.

And specifically we are asked to conduct some EYE-conometrics and

see what we can tell about the two distributions.

Well, as I just mentioned,

the distribution of the grades in the course Introduction to Probability does

not look normally distributed, and instead looks a little bit shifted to the right.

However, the distribution of the grades in Introduction to Analysis

tend to resemble a little more the structure of a normally distributed graph.

3:51

Now, we are asked the core question of this exercise,

which is to create a QQ Plot for the grades for both subjects,

by which we will be able to evaluate if the data are indeed normally distributed.

The instructions are as follows.

First, we need to sort the grades in ascending order.

Well if you look at the data specifically, I already did that for you, so

you have them already in your Excel file.

4:14

Secondly, we need to create a column for

each grade that shows the rank proportion, this means the percentile.

Don't worry, I will show you now how to do this.

Then the percentile of this candidate or,

4:30

Candidate is its rank minus 0.5.

We need to do this in order to have a center percentile and

not the percentile of the above category.

Don't worry again, I will show you how to do it specifically.

Using these percentiles, we will calculate a corresponding Z-Score.

And then we will just need to plot the Z-Score against the grades of each of

the students in each of the subjects.

5:00

So, as promised, here I will show you how to calculate the percentiles,

the Z-Scores and how to plot them.

So, here we have the student rank,

which is exactly the same as you saw at the very beginning.

Now we need to calculate the percentile.

Well, the percentile is calculated by just taking the grade of the first student,

the smallest grade student, subtract 0.5,

because we want to have the percentile of the centered student,

and divide it by 29, which is the total number of students.

This will give us, or a way to interpret this,

is to see that the student in rank or

position 7 will be better than 22.4% of the students.

If we scroll down a little bit,

student 16 will be be better than 53% of the other students.

We can do this for the rank and then we need to transform this

rank to the Z-Score in order to be able plot a QQ plot.

We can easily do this by applying the formula NORM.S.INV of the Percentile.

If we just scroll down,

we will get the Z-Score for the percentiles for every student's rank.

Then, the last step that we need to do is to plot the grades,

the actual grades in each subject of each student,

obviously ordered by this ascending ranking, against the Z-Score.

And additionally,

plot the Z-Score against the Z-Score in order to have a straight line in our plot.

6:42

If we just plot the values that we just calculated,

this is what we receive, which is our desired QQ plot.

Here we see that the blue dots, which are the grades in Analysis,

are much closer to the gray dots, which represent this straight line,

which would represent perfectly normally distributed data, and

are therefore closer to a normal distribution.

However, the grades in Probability have a clear

different shape than a straight line.

The straight line that we could have here with the grey dots, marked by the Z-Score.

And therefore, the dean of the University of Claudidorf could assume that if

somebody is cheating, then it's probably somebody in the course of Probability.

7:32

Thank you very much.

I hope that you really enjoyed the course and

learned a little bit more about statistics.

I definitely enjoyed creating these exercises for you and

recording them for you.

Hopefully, see you soon in another online course.

Thank you very much and, of course, have fun.