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.

Â