Student Data Analysis

In this assignment, you will create a dataset using Microsoft Excel for purposes of analysis later in the course. Your sample dataset will represent two student groups, A and B. Step 1 Open Microsoft Excel to a blank workbook. Double-click on the tab at the bottom of the worksheet and relabel it RES6003, Student Data, or some other relevant title. Step 2 Label Column A of the Excel spreadsheet Student Group by entering that text in Cell A1. Because these groups are categories, they are considered nominal data. Label Column B Remedial Hours in Cell B1. This column will contain interval data, which is the number of hours of remediation the student received between 0 and 20. Label Column C Project Grade in Cell C1. This column will contain ratio data representing the students grade on a major project in the class. The data are ratio because they are expressed in terms of percentage points, which is the number of points scored out of 100. Label Column D Final Grade in Cell D1. This column will contain the final grade the student earned in the class. This is also classified as ratio data because it is a percentage score. Step 3 Column A can have one of two values: A or B. Next, for purposes of this example, populate the first 25 rows with A and the next 25 rows B. Note that the column headings are on Row 1, so you will start on Row 2. Therefore, the B group will start on Row 27. Step 4 In Cell B2, enter the following formula: =RAND()*20. This formula generates a random number between 0 and 20. Round up the random value since we cant have a partial click. Use the Decrease Decimal button on the Excel toolbar in the Number ribbon to round the values to a whole number there should be no decimals. Populate the rest of the rows in Column B by grabbing the corner of the cell and dragging it all the way down the spreadsheet to Row 51. The cells in Column B should now be populated random values between 0 and 20. In Cell C2, enter the following formula: =55+RAND()*45. This formula will generate a random number between 55% and 100%. Now, grab the bottom right corner of the cell again and drag it all the way down to Row 51 of Column C. The Project Grade column should now be populated with values between 55 and 100. Repeat the procedure for the final grade in Column D, but this time, account for the project grade by assuming the student cant score a lower final grade than what they scored on their project. Therefore, enter the following formula into cell D2: =C2+(RAND()*(100-C2)). This will ensure that the final grade ends up somewhere between what they scored on their project and 100. Drag the formula down the column again to Row 51 of Column D. The Final Grade column should now be populated with random scores between the project grade and 100. Dont forget to decrease the decimal for Column C and Column D, leaving at least two decimal places. This will allow a bit more accuracy when you conduct your statistical tests. Step 5 Click on Cell B52 and enter the following formula: = AVERAGE(B2:B51). This will calculate the mean score, or average, for the Hours of Remediation column. Note that this will calculate the average for both groups. After entering the first formula, you can grab the bottom right corner of the cell again and drag the formula to the right, through cells C52 and D52. This should calculate the average scores for the project and the final grade. In a blank area of the worksheet, calculate the averages of each column for each individual group; to do this, only include the rows representing the groups you want. For instance, calculate the average Remedial Hours for each group by using the following formulas instead: Put this (Group As average) in one cell: =AVERAGE(B2:B26). Put this (Group Bs average) in another cell: =AVERAGE(B27:B51). Create appropriate labels for the data in this part of the spreadsheet. Step 6 After generating the random numbers, select all the cells, copy them to the clipboard, and from the paste menu (clipboard icon) choose Paste Values. This ensures the random numbers are not regenerated every time something changes on the spreadsheet. Copy all of the cells and paste the values into a new worksheet in the same workbook, or overwrite the formulas by pasting the values directly on top of them. Step 7 Open Microsoft Excel and install the Data Analysis ToolPak: In Microsoft Excel, click on File > Options > Add Ins. Click Go next to the dropdown labeled Excel Add-ins. Check Analysis ToolPak to enable the Microsoft Data Analysis ToolPak for Microsoft Excel. Confirm that the Data Analysis ToolPak is installed by going to the Data tab on the toolbar ribbon and ensuring the Data Analysis icon appears on the far right.