MAT152 Excel Lab 5 ADA1
docx
keyboard_arrow_up
School
Central Michigan University *
*We aren’t endorsed by this school
Course
7630
Subject
Mathematics
Date
Apr 3, 2024
Type
docx
Pages
4
Uploaded by pm198062
Lab 5 – The Normal Distribution
The objectives of this lab are to work with the normal distribution by:
Including a normal curve for a dataset
Finding areas under the normal curve
Given an area under the normal curve, finding the values for the random variable
These topics are covered in chapter 7 of the text.
Lab practice: Open MAT152 Pulses.xlsl
If you do not have access to Excel at home
, Login to CPCC Cloud Link
(cloudgw.cpcc.edu)
Go to Student General Desktop.
Click on the Windows flag in the bottom left and then open Brightspace.
Go to Lab 4 and double click on MAT152 Pulses
If you have access to Excel at home, open our MAT 152 Bright Space course, under Excel Lab 5 double click on the file MAT152 Pulses
The file should appear in the Excel window. Including a normal curve on the histogram
To find the normal distribution
First we need to find the mean and standard deviation which we learned how to do in Lab 3.
In cell, C1 type Mean. Hit Tab.
In cell, D1 type Standard Deviation. Hit Tab
In cell, E1 type Normal distribution. Hit Enter.
Select C2, under Mean. Type =Average(B:B). You get B:B by double clicking the heading B above Pulses. We are working with Pulses because we need quantitative data to make a Normal Distribution.
Select D2, under Standard Deviation. Reminder that we have a sample. Therefore, type =STDEV.S(B:B) We choose the StDEV.S because it is a sample not the population.
Now we are ready to calculate the values for the Normal distribution function. o
Excel has a built-in formula. = Norm.Dist(x, mean, standard_dev, cumulative)
X: Defines for which value you want to find the distribution.
Mean: The arithmetic means value for the distribution.
Standard Dev: The standard deviation for the distribution.
Cumulative: True means a cumulative distribution function and false means a probability mass function. o
In E2 under the Normal Distribution Heading, o
Type =NORM.DIST(B2,C2,D2,TRUE)
However, we need to change this input so the mean and standard deviation stay
the same in every calculation.
Place your cursor beside C2 hit F4 button. Then place cursor beside D2 and hit F4. This changes our formula to =NORM.DIST(B2,$C$2,$D$2,TRUE).
When you press enter, nothing changes. However, we are now ready to click on the bottom right corner of the E2 box and drag to the bottom of
the dataset.
This gives you the area to the left of the data value in column B.
What if we wanted the area to the right, =1-E2
You can add this to F column if you would like.
Then drag the box down to the bottom of the dataset.
How to Make a Normal Distribution Graph in Excel
First we have to add one more column, Type Normal Dis Graph in cell H1.
In H2, type =NORM.DIST(B2,$C$2,$D$2,False) This gives the probability at each individual point.
Again, hold the right corner of cell H2 and drag to the bottom of the data set.
Select the Pulse Column to highlight. Then click on the Home tab. Then click on Sort and Filter and choose Smallest to Largest. If you get the Sort Warning, choose expand your selection.
To make the table a normal distribution graph, select the table columns Pulse and Normal Dis Graph Column. First click on heading B above Pulse. Then click on Ctrl and hold down and select heading H above Normal Distribution Graph.
Go to Insert Tab and click on Recommended Charts.
o
Click on All Charts at the top. o
Select the X Y Scatter, then click the 3
rd
scatter plot picture that says Scatter with Smooth Lines.
o
Then select the second option the Normal Distribution. Then click OK.
We will now open a second Excel worksheet to complete remainder of the lab. Go to File-New-Double click on the blank worksheet
.
Finding areas under the normal curve According to the College Board, the mean SAT Math score for college bound seniors in 2010 was 511, with a standard deviation of 104 (
Link to College Board Site
). Assuming that SAT scores are approximately normally distributed, we’ll use this information with Excel to answer the following:
What is the probability that a randomly selected NC senior got a score below 450?
In Excel, Select Cell A1
This is the format that we need in the cell =Norm.Dist(x, mean, standard dev, cumulative
X is going to be your upper bound. This will find the area to the left of the x-value.
Type= Norm.Dist(450,511,104,True) The true statements will add all the probabilities to the left of 450.
You should get 0.278757
What is the probability that a randomly selected NC senior got a score higher than 590?
In Excel, Select Cell A2
This time we want to find area to the right of 590. Excel does not let you input lower bound and upper bound like the TI Calculator.
Therefore we must use the formula 1-area to the left
In Cell A2, Type =1- Norm.Dist(590,511,104,True)
Your answer should = 0.223742
What is the probability that a randomly selected NC senior got a score between 560 and 580?
This time we want to find the area between 560 and 580. Again, Excel will not let us type in the lower and upper bounds.
Instead, we will Find the (area to left of 580) minus (area left of 560)
In Call A3, Type =Norm.Dist(580,511,104,True)- Norm.Dist(560,511,104,True)
Your answer should be 0.0652
o
Given an area under the normal curve, finding the values for the random variable
What SAT score identifies the bottom 20% of NC seniors?
Since we have an area under the curve and are looking for the value for a variable, we need to use the inverse functions.
To use the inverse function in Excel, =Norm.Inv(probability to the left, mean, standard deviation)
In Cell A4, type = Norm.Inv(.20,511, 104)
Your answer should be 423.47
What SAT score identifies the top 15% of NC seniors?
Reminder that Excel can only put in area to the left. Since we are talking about the top 15%, this would be the area to the right of our X value.
First find area to the left. =1-.15= .85
Now we are ready to use our formula in Excel
Type in Cell A5, =Norm.Inv(.85,511, 104)
Your answer should be 618.789
What SAT scores identify the middle 75% of NC seniors?
Reminder that Excel can only put in area to the left. You are going to have to do this in two
parts because you will have 2 X values.
First find the area outside the middle 75% = 1-.75= .25
Divide the .25/2 so we have equal area in both tails.
o
.25/2 = .125
First x-value:
In Cell A6, type =Norm.Inv(.125,511, 104)
=391.36
Second x-value:
First figure out the area to the left of X
2
= .125(in the tail)+.75 in the middle = .875
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
In Cell A7, type=Norm.Inv(.875,511, 104)
=630.64
This completes the practice portion of the lab. Continue to scroll down to the following page for the Lab assignment.
Lab Assignment to be completed:
1.
Open the file LabData.xlsx
. Located in your Bright space course. 2.
For the variable Years with current employer (employ):
a.
Create the normal curve
3.
Open a new, blank data window.
Standardized adult IQ test scores are known to be normally distributed, with a mean of 100 and a standard deviation of 15. 4.
What is the probability that a randomly selected adult:
a.
Has an IQ score lower than 90?
b.
Has an IQ score greater than 120?
c.
Has an IQ score between 80 and 110?
5.
Find the IQ scores that identify:
a.
The bottom 15%,
b.
The top 20% c.
The lower and upper boundary for the middle 70% Complete the Lab 5 Quiz
Your grade on each lab will be the score you get on the lab quiz. The quiz is in the Labs folder in Bright Space. Keep all of your Excel windows open, since the quiz will ask you questions about their contents. When you’ve answered all the questions, be sure to click Submit to record your results.
This completes the work for Lab 5. Please remember to log off the computer before you leave.