QuestionDSCI 3321Computer Assignment 2 – Spring 2019

02 May QuestionDSCI 3321Computer Assignment 2 – Spring 2019

Question

DSCI 3321

Computer Assignment 2 – Spring 2019

Sections 3 and 4 – 2019

Section 5 – 2019

1. Excel has the capability of generating probabilities for areas of the normal

distribution Suppose data are normally distributed with a mean of 100 and a standard deviation of 15. What is the probability that an IQ is 133 or more? In Excel, start by selecting the FORMULAS tab. Now click on fx , the Insert Function. From the second box, Or select a category, select Statistical. From the next box (middle of the page), scroll down the alphabetical list and select NORM.DIST. Highlight NORM.DIST and click OK. Now you will get the NORM.DIST dialog box. The NORM.DIST box asks for 4 things. In the first line, place the value of x, the score you are interested in (here, 133). In the second line place the value of the mean (here, 100). In the third line, place the value of the standard deviation (here, 15). In the fourth line, place the word, TRUE. We always use TRUE with the normal distribution. In NORM.DIST, the word, false, just produces the height of the curve.

The result is the cumulative probability that the value of x or less will occur. For example, for the IQ score of 133, the mean is 100, and the standard deviation of 15, a cumulative probability of .9861 is determined. That is, 98.61% of all IQ’s are less than 133. In the case of this problem, we want the probability that the IQ is 133 or more. We can easily get this by taking 1-.9861 or .0139 or 1.39% are greater than an IQ of 133 by using the formula bar. Work the following problems using Excel. Along with each answer, print out the formula used from the formula bar at the top of the spreadsheet. You can copy and paste it in Word. It will look like this:

1-NORM.DIST(133,100,15,TRUE)

Work: 6.6 a, b, d, e – page 183 of text;

6.47 a, b, c, d – page 198 of text

Please print out your answers.

2. Generating random normal distribution numbers and graphing them.

Generate one column of 100 random numbers from a normal distribution with a mean of 100 and a standard deviation of 15. Construct a histogram from these values.

Start with the Data tab on the menu bar. Now on the right end of the “data” menu bar, select Data Analysis. From there, select RANDOM NUMBER GENERATION. From the dialog box that appears, under distribution, select NORMAL. The number of variables in line one is 1. The number of random numbers is 100. Place the mean and the standard deviation in the boxes where they ask for them. Print out the numbers and create a histogram (using the chart output option on the histogram dialog box). Use the bin range of 40, 55, 70, 85, 100, 115, 130, 145, 160. Load these bin values in a column beside the random numbers. In the Histogram dialog box, when asked where the bin values are, give it the location of these values. Be sure to check “Chart Output” in the histogram dialog box. Print out the graph and the numbers.

3. Consider the following problem related to Chapter 12 of the text.

City utility people have observed over time that as temperatures warm up, water consumption in the city increases. Examine the following data gathered on various days over a yearly period. For each of the eight days, the high temperature and the water usage in millions of gallons is given.

Temperature Water Use

(degrees Fahrenheit) (millions of gallons)

103 219

39 56

77 107

78 129

50 68

96 184

90 150

75 112

Construct a scatter plot of the data, then add a trend line.

Directions for scatter plot and trend line:

To get a scatter plot of the data in Excel, click and drag over the data, go to the Insert

Tab and create a Scatter Plot of the data. To place the regression line on the scatter

plot, right hand click on one of the points on the graph. A command, add trendline,

should appear. This is your regression line.

Now, using Data Analysis, conduct a regression analysis of these data to develop a

model for predicting Water Use by Temperature.

Directions in Excel: Use the Data tab, select Data Analysis and then

select Regression. In the regression dialog box, enter the location of the y variable

and of the x variable. Check the “labels” box since we have titled each column.

Check the box “residuals” at the bottom of the dialog box. Click OK and the

regression output should appear including the residuals.

4. Go to Computer Assignments in Blackboard. Under Computer Assignment 1, click

on and bring up the class database in Excel. Using Excel, compute four correlations:

i. Between Miles and Minutes

ii. Between Age and Work Hours

iii. Between Credit Hours and Books

iv. Between Miles and Books

Directions in Excel: Use the Data tab, select Data Analysis and then

select Correlation. In the correlation dialog box, click over all of the first seven

variables. Check the “labels” box since we have titled each column.

Click OK and a correlation matrix will appear. Studying the matrix, you will see all

21 possible correlations. Answer the four specific correlations asked for above.

Comment briefly on each one. Did you see anything else interesting?