Statistics Project

Assignment 1: Let's begin! Using the Excel attachments, open Excel. Open Part 1: BBSalary. You will see average salary, median salary and minimum salary by year from 1969 (B2) to 2002(B35)Follow the following steps:



Step 1: Select "Tools" pull-down menu

Step 2: Choose "Data Analysis"

Step 3: Choose "Descriptive Statistics" from the list of Analysis Tools

Step 4: When the Descriptive Statistics dialog box appears:

Enter B1:B35 in the "Input Range" box

Select "Grouped By Columns"

Select"Labels in First Row"

Select "Output Range"

Enter E1 in the "Output Range" box (to identify the upper left-hand corner of the section of the worksheet where the descriptive statistic will appear)

Click on "Summary Statistics"

Click "OK"

Save your results and load into the Assignment Folder



Assignment 2:

The Excel function for computing binomial probabilities is BINOMDIST. It has four arguments: x (the number of successes), n (the number of trials), p (the probability of success) and cumulative. False is used for the fourth argument if we want the cumulative probability of x successes, and True is used for the fourth argument if we want to compute the probability of x or fewer successes. On an Excel sheet let p = .5 for the probability of making a basketball shot. Assume we are make 10 attempts to make a basket. In row 1 we have Number of Trials (n) in col A and 10 in col B. In row 2 we have Probability of Success (p) in col A and 0.5 in col B. In row 4 column B, we have x. In column C we have f(x). We want to complete rows 5 to 15 with our trials



Row 5 will look like this:

Column B: 0

Column C: = BINOMDIST(B5,$B$1,$B$2, FALSE)



For the next row the only changes are:

Column B: 1

Column C: you will substitute B6 for B5)



(remember to plug in x = 2,3,4,5,6,7,8,9,10)



Part 2 of this Assignment uses the Beer File. Open the Beer File. 



Note that in column B we have the price of 69 different beers found in the market. We want to do an interval estimate of the population mean (cost of a beer) using the normal distribution. In Row 4 begin by typing the following: (Do NOT TYPR "periods"

Column G..............Column H....................

Sample Size...........=Count(B2:B70)...............

Mean..................=Average(B2:B70).............

Standard Deviation....=STDEV(B2:B70)...............



Confidence Coefficient..0.95 

Alpha................. =1-H8

Z Value............... =NORMSINV(1- H9/2)



Standard Error........ =H6/SQRT(H4)

Margin of Error....... =H10*H12



Point Estimate....... =AVERAGE(B2:B70)

Lower Limit.......... =H15-H13

Upper Limit.......... =H15+H13
---------------------------------------------------------------------

Assignment 3: Two Population Means

Large Sample Case-Use the Salaries data from the attachments
m
Select the Tools pull-down menu

Choose Data Analysis

When the Data Analyis dialog box appears:

Choose z-Test:Two Sample for Means

Click OK

When the z-Test: Two Sample for Means dialog box appears:

Enter B1:B115 in the Variable 1 Range box

Enter C1:C115 in the Variable 2 Range box

Enter 0 in the Hypothesized Mean Difference box

Enter k in the Variable 1 Variance box (you must use another program to calculate the variance first -replace k with the value)

Enter m in the Variable 2 Variance box (you must use another program to calculate the variance first-replace m with it)

Select Labels

Enter .05 in the Alpha box

Select Output Range and enter D1 in the box

Click OK



For the Small Sample Case - Use attached files (Textbooks).



Select the Tools pull-down menu

Choose Data Analysis

When the Data Analyis dialog box appears:

Choose t-Test:Two Sample Assuming Equal Variances

Click OK

When the t-Test: Two Sample Assuming Equal Variances dialog box appears:

Enter A1:A16 in the Variable 1 Range box

Enter B1:B16 in the Variable 2 Range box

Enter 0 in the Hypothesized Mean Difference box

Select Labels

Enter .05 in the Alpha box

Select Output Range and enter C1 in the box

Click OK 



Assignment 4: Analysis of Variance

Analysis of Variance -Use the Circuits file 

Select the Tools pull-down menu

Choose Data Analysis

Choose Anova: Single-Factor from the list of Analysis Tools

Click OK

When the Anova: Single Factor dialog box appears:

Enter A1:F31 in Input Range box

Select Columns

Select Labels in First Row

Select Output Range and enter A35 in the box

Click OK 





Assignment 5: Regression Analysis (Sporting Goods)



Select the Tools pull-down menu

Choose Data Analysis

Choose Regression from the list of Analysis Tools

Click OK

When the Regression dialog box appears:

Enter D1:D39 in the Input Y Range Box

Enter B1:B39 in the Input X Range Box

Select Labels

Select Confidence Level

Enter 99 in the Confidence Level box

Select Output Range

Enter A42 in the Output Range box