Excel Z-Score Assignment

Excel Z-Score Assignment

Using Excel to Calculate Normal Distribution Areas, Z Scores, and Normal Quantile Plot

In this week’s lesson, we will be learning how to use Excel to calculate normal distribution areas, z scores, and normal quantile plot.

Resources to consult: See the videos marked 6-1, 6-5  Triola Stats Excel Videos

Check tips on how to do your homework-help-services/

Assignment

Part 1

View the video and complete the following steps on a blank Excel worksheet.  6-1 Using Excel to Find Normal Distribution Areas [03:05]

In cell A1, find the probability that the z score is less than 1.27 when the mean is 0 and the standard deviation is 1.

Save the completed worksheet as  W5_6-1_Normal.xls

Part 2

Excel Z-Score Assignment

View the video and complete the following steps on a blank Excel worksheet.  6-1 Using Excel for Finding a z Score From a Known Area [03:03]

In cell A1, find a z score corresponding to the 95 percentile when the mean is 0 and the standard deviation is 1.

Save the completed worksheet as  W5_6-1_ZScore.xls

Submission

You are to submit the following files in the assignment dropbox:

·  W5_6-1_Normal.xls

·  W5_6-1_ZScore.xls

1. How do you calculate the probability for a z-score of 1.27 in Excel?,

2. What function do you use in Excel to find a z-score from a percentile?,

3. What value is returned for the 95th percentile z-score?,

4. How do you label and save your Excel worksheets?,

5. What files need to be submitted?

General Comprehensive Answers:

1. Calculating Probability for z = 1.27 in Excel:
In Part 1, open a blank Excel worksheet.
In cell A1, enter the following formula:

This function calculates the cumulative probability that a z-score is less than 1.27 when the mean is 0 and standard deviation is 1.

• The result will be approximately 0.8980, or 89.80%.

2. Finding a z-Score for the 95th Percentile in Excel:
In Part 2, open another blank Excel worksheet.
In cell A1, enter the formula:

This calculates the z-score that corresponds to the 95th percentile.

• The result will be approximately 1.6449.

3. File Naming and Saving Instructions:

• Save Part 1 as W5_6-1_Normal.xls

• Save Part 2 as W5_6-1_ZScore.xls

4. Files to Submit:
Upload both files into the assignment dropbox:

• W5_6-1_Normal.xls

• W5_6-1_ZScore.xls

• Excel Z-Score Assignment