Math 40 Lab 2: Descriptive Statistics
Notes
• You are to complete the following work in Excel. These instructions are for my version of Excel on a PC. Depending on your version of Excel or if you’re using a MAC, certain aspects may very slightly.
• Once completed, you are required to upload your Excel file into the Lab 2 Assignment in Canvas.
• If you don’t have Excel, you may download it for free through Office 365.
http://www.laspositascollege.edu/students/office365.php
• You need to make sure that you have completed all work yourself, and that you’re not sharing files with other students.
Random Number Generator
1. Generating Random Numbers
• This is the same random number generator that you used in Lab 1, but please make sure to generate a new set of numbers.
• You’re going to generate 50 random integers between 1 and 100, inclusive.
• Since random numbers generated in Excel will often change with each new calculation, you’re going to generate your random numbers outside of Excel.
• Follow this link https://www.randomizer.org to go to the Research Randomizer site.
• At the site, enter the following information.
- ❖ How many sets of numbers do you want to generate? 1
- ❖ How many numbers per set? 50
- ❖ Number range? 1 to 100
- ❖ Do you wish each number in a set to remain unique? No
- ❖ Do you wish to sort the numbers that are generated? Yes, least to greatest.
- ❖ How do you wish to view your random numbers? Place markers off
- ❖ Select Randomize Now!
❖ You should now see your list of random numbers. Nice work!
2. Entering your random numbers into Excel.
• On the results page, you have the option of either printing out your numbers or downloading them.
• Selecting download, will create a csv file, which you can open.
• Open the csv file and save it as an Excel file.
• If for some reason you’re not able to save the document as an Excel workbook, simply open a new Excel file and type in your list of random numbers starting in cell A5.
3. Administrative
• Delete anything that appears in rows 1–4.
• Type your first name and last name in cells A1 and B1, respectively.
• Enter your course name in cell A2 and your section number in cell B2.
• Type Lab 2 in cell A3.
• Leave row 4 blank.
• Your data should begin in cell A5 and end in cell A54.
Create Two Transformed Lists
• You’re going to create two new lists of numbers based upon your random numbers in column A.
• For the first new list, you’re going to add 4 to each of your random numbers.
o Starting in cell B5, type “=”, click on cell A5, type “+4”, and hit Enter. The number in cell B5 should be 4 more than the first random number in cell A5.
o Now let’s copy this formula to generate the rest of the transformed list.
o Click on cell B5. Hover the cursor over the lower right–hand corner of the cell until you see the black plus sign.
o Holding the mouse down, you can copy the formula by dragging down to cell B54. Once you release the mouse, the new numbers should be created. Please make sure that they’re 4 more than the original random numbers in column A.
• For the second new list, you’re going to multiply each of your random numbers by 4.
o Starting in cell C5, type “=”, click on cell A5, type “*4”, and hit Enter. The number in cell C5 should be 4 times the first random number in cell A5.
o Copy this formula to generate the rest of the transformed list.
o Please make sure that they’re 4 times the original random numbers in column A.
Descriptive Statistics
• You’re going to use Excel functions to calculate descriptive statistics for each set of numbers in columns A, B, and C.
• Since are data is from a random sample, you’ll want to make sure to use the sample versions of the functions. Please note that functions may vary slightly due to your version of Excel.
• Starting in cell E5, put the function labels listed below in the left column.
• Calculate the descriptive statistics of each set of numbers in columns A, B, and C.
o Column A: Starting in cell F5, use the formulas listed below in the right column.
o Column B: Starting in cell G5, use the formulas listed below referencing cells B5:B54.
▪ Z–score of 5th smallest value: =(B9–G5)/G6
o Column C: Starting in cell H5, use the formulas listed below referencing cells C5:C54.
▪ Z–score of 5th smallest value: =(C9–H5)/H6
• Round the z–score to two decimals and all other results to one decimal. In Excel rounding is for display only. The actual values are still stored in the cells.
o Mean (average) =average(A5:A54)
o Standard Deviation =stdev.s(A5:A54)
o Variance =var.s(A5:A54)
o Minimum =min(A5:A54)
o 1st Quartile =quartile.exc(A5:A54,1)
o Median =median(A5:A54)
o 3rd Quartile =quartile.exc(A5:A54,3)
o Maximum =max(A5:A54)
o 40th percentile =percentile.exc(A5:A54,40/100)
o Z–score of 5th smallest value =(A9–F5)/F6
Type your answers to the following questions at the bottom of your spreadsheet.
1. Compare the descriptive statistics of the numbers in columns A and B.
a. What happened to the z–score of the fifth–smallest value?
b. What happened to the standard deviation and variance?
c. What happened to the other statistics?
2. Compare the descriptive statistics of the numbers in columns A and C.
a. What happened to the z–score of the fifth–smallest value?
b. What happened to the variance?
c. What happened to the other statistics?
3. Reflect on your experience with completing this lab assignment.
