excel workbooks
Excel Workbooks - Instructions Part I: Open a new Excel workbook and load the “Analysis ToolPak” in Excel to make available the “Data
Analysis” Program. Step 1: Open a new Excel workbook. Name the workbook “ExcelWorkbook”.
Step 2: Click on the “Data” tab in your Excel workbook. Check to see if the “Data Analysis” tool is available in your window.
If the tool is not available, you will need to install and load the “Analysis ToolPak” as follows: Step 1: Click the File tab, click Options, and then click the Add-Ins category.
• If you're using Excel 2007, click the Microsoft Office Button Office button image , and then click Excel Options
Step 2: In the Manage box, select Excel Add-ins and then click Go. • If you're using Excel for Mac, in the file menu go to Tools > Excel Add-ins.
Step 3: In the Add-Ins box, check the Analysis ToolPak check box, and then click OK.
· If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.
· If you are prompted that the Analysis ToolPak is not currently installed on your computer, click
Yes to install it.
1. Part II: Use random.org to generate a data set. Use Excel to construct a histogram of your data set and to compute the mean and standard deviation of your data set.
Sheet 1: Generate a data set.
Step 1: Go to Random.org. Scroll down to “Numbers” and click on “Integer Generator”. Generate 50 random integers between 1 and 100. Choose “Format in 1 Column”. Click on “Get Numbers”. Copy the fifty random numbers.
Step 2: Paste the fifty random numbers into Column A (Begin at Cell A1) of Sheet 1 of the Excel workbook. Right click on the tab at the bottom of Sheet 1 and rename the sheet “Data Set”. You should now have a (population) data set composed of fifty integers between 1 and 100.
Sheet 2: Construct a Histogram of your Data Set. Step 1: Right click on the tab at the bottom of Sheet 2 of your Excel worksheet and rename the sheet
“Histogram of Data Set”.
Step 2: Copy your data set from column A of your “Data Set” sheet into column A of your “Histogram of Data Set” sheet.
Step 3: Run “Descriptive Statistics” on your data set. 1) Click on cell C1. 2) Select “Data -> Data Analysis -> Descriptive Statistics -> OK”. The “Descriptive Statistics” box
should open. 3) Click on the arrow in “Input Range” in the box and select all the cells that contain your data set.
Hit enter. 4) Select “Output Range” in the box. Click on the arrow in “Output Range” and select cell C1. Hit
Enter. 5) Select “Summary Statistics” in the box. Click on OK.
The summary statistics of your data set should now appear beginning at Cell C1.
Step 4: Construct 4 bins (class intervals) for your data set. 1) Click on Cell C17 and name it “Number of Bins”. 2) Click on Cell D17 and input the number 4. 3) Click on Cell C18 and name it “Bin Width”.
4) Click on Cell D18. Type “= Range / Number of Bins ”, where Cell D11 should be selected for Range and Cell D17 should be selected for Number of Bins . Hit Enter.
5) Click on Cell F1 and name it “My Bins”. 6) Click on Cell F2. Type “= Minimum ”, where Cell D12 should be selected for Minimum . Hit Enter. 7) Click on Cell F3. Type “= F2 + Bin Width ”, where Cell F2 should be selected for F2 and the
numerical value of the bin width should be typed into the cell in place of Bin Width . Hit Enter. 8) Click on the bottom right corner of Cell F3 and drag down to Cell F7.
The numbers in Cell F2 to Cell F7 are the boundaries of your histogram bars. The range of the values in these cells should be close to the range of your data values.
Step 5: Construct a histogram of your data set. 1) Click on Cell H1. 2) Select “Data -> Data Analysis ->Histogram -> OK. The “Histogram” box should open. 3) Click on the arrow in “Input Range” in the box and select all the cells that contain your data set.
Hit enter. 4) Click on the arrow in “Bin Range” in the box and select all the cells that contain your bins. Hit
enter. 5) Select “Output Range” in the box. Click on the arrow in “Output Range” and select cell H1. Hit
Enter. 6) Select “Chart Output” in the box. Click on OK.
A table with columns for bin and frequency should now appear at Cell H1 and a histogram of your data set should appear near Cell K1.
Part III: Use Excel to take fifteen random samples of size 10 from your data set, to find the mean of each of the fifteen samples, to construct a histogram of the fifteen sample means, and to compute the mean and standard deviation of the fifteen sample means.
Sheet 3: Samples of Size 10 Step 1: Right click on the tab at the bottom of Sheet 3 of your Excel worksheet and rename the sheet
“Samples of Size 10”.
Step 2: Copy your data set from column A of your “Data Set” sheet into column A of your “Samples of Size 10” sheet.
Step 3: Name Cell C1 to Cell Q1: “Sample 1”, “Sample 2”, ...,“Sample 15”, respectively. Name Cell S1 “Sample” and Cell T1 “Mean”. Name Cell S3 to Cell S17 “Sample 1”, Sample 2, ... “Sample 15”, respectively.
Step 4: Find Sample 1. 1) Click on Cell C3 2) Select “Data -> Data Analysis -> Sampling -> OK”. The “Sampling” box should open. 3) Click on the arrow in “Input Range” in the box and select all the cells that contain your data set.
Hit enter. 4) Select “Random” in the box. Type “10” in the “Number of Samples” box. 5) Select “Output Range” in the box. Click on the arrow in “Output Range” and select cell C3. Hit
Enter. 6) Click on OK.
You should now have a random sample of size 10 taken from your data set.
Step 5: Repeat Step 4 fourteen times to find Sample 2 to Sample 15, replacing the “Output Range” with the appropriate cell for the sample.
Step 6: Find the Sample Means.
1. 1) Click on Cell T3. Type “=AVERAGE( Cells )”. Select the cells that contain Sample 1 for Cells . Hit
Enter. The number in Cell T3 should be the mean of Sample 1.
2. 2) Click on Cell T4, and Repeat 1), selecting now the cells that contain sample 2 for Cells . The
number in Cell T4 should be the mean of Sample 2.
3. 3) Repeat for Cell T5 to Cell T17 for the means of Sample 3 to Sample 15, respectively.
You should now have fifteen sample means of the fifteen random samples of size 10 taken from your data set.
Sheet 4: Histogram of Size 10 Step 1: Right click on the tab at the bottom of Sheet 4 of your Excel worksheet and rename the sheet
“Histogram of Size 10”.
Step 2: Name cell A1 “Sample” and Cell B1 “Mean”. Name Cell A3 to Cell A17, “Sample 1”, “Sample 2”, ..., “Sample 15” respectively. Copy the means of Sample 1 to Sample 15 from the “Samples of Size 10” sheet into Cell B3 to Cell B17, respectively. You will need to paste “values” or you will get an error.
Step 3: Run “Descriptive Statistics” on the means or your fifteen samples of Size 10. 1) Click on cell D1. 2) Select “Data -> Data Analysis -> Descriptive Statistics -> OK”. The “Descriptive Statistics” box
should open. 3) Click on the arrow in “Input Range” in the box and select all the cells that contain the ten sample
means. Hit enter. 4) Select “Output Range” in the box. Click on the arrow in “Output Range” and select cell D1. Hit
Enter. 5) Select “Summary Statistics” in the box. Click on OK.
The summary statistics of your sample means should now appear beginning at Cell D1
Step 4: Construct 4 bins (class intervals) for the means or your fifteen samples of Size 10. 1) Click on Cell D17 and name it “Number of Bins”. 2) Click on Cell E17 and input the number 4. 3) Click on Cell D18 and name it “Bin Width”.
4) Click on Cell E18. Type “= Range / Number of Bins ”, where Cell E11 should be selected for Range and Cell E17 should be selected for Number of Bins . Hit Enter.
5) Click on Cell G1 and name it “My Bins”. 6) Click on Cell G2. Type “= Minimum ”, where Cell E12 should be selected for Minimum . Hit Enter. 7) Click on Cell G3. Type “= G2 + Bin Width ”, where Cell G2 should be selected for G2 and the
numerical value of the bin width should be typed into the cell in place of Bin Width . Hit Enter. 8) Click on the bottom right corner of Cell G3 and drag down to Cell G7.
The numbers in Cell G2 to Cell G7 are the boundaries of your histogram bars. The range of the values in these cells should be close to the range of your sample means.
Step 5: Construct a histogram of your data set. 1) Click on Cell I1. 2) Select “Data -> Data Analysis ->Histogram -> OK. The “Histogram” box should open. 3) Click on the arrow in “Input Range” in the box and select all the cells that contain the fifteen
sample means. Hit enter. 4) Click on the arrow in “Bin Range” in the box and select all the cells that contain your bins. Hit
enter. 5) Select “Output Range” in the box. Click on the arrow in “Output Range” and select Cell I1. Hit
Enter. 6) Select “Chart Output” in the box. Click on OK.
A table with columns for bin and frequency should now appear at Cell I1 and a histogram of your sample means should appear near Cell L1.
Part IV: Repeat the instructions for Sheet 3 and Sheet 4 for samples of sizes 30 and 100. Sheet 5: Repeat the instructions for Sheet 3 for Samples of Size 30
Sheet 6: Repeat the instructions for Sheet 4 for Histogram of Size 30 Sheet 7: Repeat the instructions for Sheet 3 for Samples of Size 100
Sheet 8: Repeat the instructions for Sheet 4 Histogram of Size 100 Part V: Submit “ExcelWorkbook” via the “Excel Workbook” page on Ulearn to be graded.