Transfer the file in /afs/glue/class/fall2005/cmsc/102/0101/public/P6/Stage1.xls to your PC.

You will now begin to modify this worksheet.

1. First, replace the contents of cell A1 (which currently says "Put YOUR UID Here") with your own University ID.
2. There are several empty columns with column headings that you need to populate with the correct data. The ExamAvg column needs to have the average of the two exams. The ProjAvg column needs to have the average of the three projects. The WeightedAvg column needs to have the weighted average of the exam average and project average, where the exam average is worth 57% of the total and the project average is worth 43% of the total. Make sure to use the appropriate FUNCTIONS to calculate things where possible. Remember that once you have entered a function or formula into a cell, you are able to copy that into other cells as needed.
3. Column M will need to contain the LetterGrade for each student. The first step for this is to go down to row 80 and type Cut-Offs into cell A80. The grades will be A, B, C, D, and F. The cutoffs are 90, 80, 70 and 60. Place these cutoffs as values into cells B80-E80. For example, put the value 90 into B80 and 80 into C80. This would allow you to change the cutoffs later if you decide you want to change the number of people who will receive A's for example. Finally, in column M use an IF function to assign a letter grade based on the cutoffs that you just typed in. You must use absolute cell references so that when you copy the formula, the cell references to the cutoff values will be fixed.

Once you have completed this stage, we will require the following printouts showing your work:

Copy the entire Sheet1 worksheet to the Sheet2 worksheet.

On the Sheet2 worksheet, sort the worksheet based on the students' grades on exam 1. Sort the grades in ascending order. You can do this by highlighting the area to sort (this needs to be all of the data, not just the sort field) and the using DATA-SORT and selecting the appropriate column. This will reorder the students based on their grades on the first exam.

Next, you need to create a graph of the grades on exam 1. To do this, use INSERT - CHART. Select to create a Column-based 2-D chart. To specify the data range, click on the small button to the right of the text-entry box for "data range" to temporarily return to the spreadsheet to be able to select the ranges. Use the mouse to click and drag to select the parts of the column with the aliases and the parts of the column with the exam 1 grades. Since these are non-contiguous areas, you will need to depress the CONTROL button on the keyboard as you highlight each of the two regions. After doing this, click on the button to the right of the (now abbreviated) data range box. Finally, make sure you select to place the chart as a new sheet.

Print this chart and label it as SP-3.

Now check to see if there is a correlation between the students' grade on the first exam and the grade for the course. To do this create another chart, but this time make it an XY scatter plot with the data points connected by lines. For the series, use the column that has the exam 1 grades and the column that has the course grades.

This graph will show whether there is (a)a negative, (b)a positive or (c)no correlation between how students did on the first exam and how they did in the class.

There is a positive correlation if the graph is roughly linear and has a positive slope. There is a negative correlation if the graph is linear and has a negative slope. If the line is noticeably jagged then there is no correlation. (This is NOT a precise way to tell if two data sets are correlated, but the ability to estimate is useful.)

Print this graph and label it as SP-4. Also, write on the printout of the graph what type of correlation you believe there appears to be.

Transfer the file in /afs/glue/class/fall2005/cmsc/102/0101/public/P6/Stage3.xls to your PC.

You will now begin to modify this worksheet.

Go to the Summaries worksheet page and enter the appropriate formulae to cells B3..B5 and C3..C5 to bring the exam averages from the other three worksheets into this worksheet.

Print the Summaries worksheet page displaying the calculated values and label this SP-5.

Print the Summaries worksheet page displaying the value rules. Again, to get the cell formulae to print, you go to the OPTIONS selection under the TOOLS menu. From the options dialog box, on the VIEW tab, under "Window options" select "Formulas". That will have the cells show the actual formula and function entries in the spreadsheet. Reformat the cell widths so that all cells are wide enough that the ENTIRE formulae can be seen. If there are multiple pages printed, staple them togther. Label these SP-6 After printing this, go in and deselect that option so that the results are displayed again as they were before. Reformat column widths as needed.

1. Bring the data into Microsoft Excel as a spreadsheet using one of the techniques we learned in class (using MS Word).
2. Sort the worksheet using Calories as the primary field.
3. Now that the data is sorted, go to the bottom of the data, and add three new rows for the column with the secondary field Sodium. The first new entry should be the average of the values in that column. The second new entry should be the standard deviation of the values. The third new entry should be the slope of the linear regression line for that data.
4. Now, use the CORREL function to actually calculate the correlation between the primary and secondary data sources, storing this value in one of the unused cells in the worksheet.
5. Add your University ID number to an unused cell on the worksheet.
6. Print out the full worksheet and label it SP-7. Again, if there are multiple pages, staple them together.
7. Create an XY plot (using the primary and secondary columns) to show the correlation between the two variables in the study. Place the plot on a new sheet and from the plot guess what kind of a correlation (if any) exists between the two variables and state why you think this is the case. Then calculate the actual correlation between the two variables on the same sheet and print out the sheet with the actual correlation and your guess on it. Print this XY plot and label it SP-8.

          Rating      Number of Votes
            10              244
             9             2948
             8             2228
             7              312
             6              765
             5               57
             4             1209
             3              341
             2              129
             1             3259

Near the top of the spreadsheet in column L, place the labels:

Web Accessibility