MAT152 Excel Lab 5 ADA1

.docx

School

Central Michigan University *

*We aren’t endorsed by this school

Course

7630

Subject

Mathematics

Date

Apr 3, 2024

Type

docx

Pages

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.

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