CMSC 102 - Project 6 - Fall 2006
Spreadsheets

Due electronically by 11:59 pm on December 12th.

Overview

For this project you will need to create and/or modify various spreadsheet files and save results in pdf files. Note that while you do not need to turn in a copy of the spreadsheets files, it is recommended that you save the files at each stage for your own benefit while doing the project.

In this project you are expected to use the techniques you learned in Project #1 to create pdf files. For example, if you are using Windows, this implies you will "print" the file using "CutePDF Writer" which will create the pdf file for you. This is what we are referring to when we ask you to create/save/print a file as a pdf file.

Once you have completed all the required tasks, you will create a zip file that includes all the necessary pdf files and then you will submit it using the submit server (http://submit.cs.umd.edu). The deadline to submit the project is Tuesday December 12th, 2006 (11:59 pm).

Specifications

Stage 1

Transfer the file in Stage1.xls to your PC and complete the following tasks using the downloaded file.

  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.
  4. Create a pdf file named SP1.pdf that shows the results of all your calculations present in the worksheet.
  5. Create a pdf file named SP2.pdf that shows the value rules present in the worksheet. To get the cell formulae to display, 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. 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.

You will submit the SP1.pdf and SP2.pdf files to us as part of the ZIP file for this project.

Stage 2

  1. Copy the entire Sheet1 worksheet you have been working on to the Sheet2 worksheet.
  2. 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.
  3. 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. If you need to highlight 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. Make sure you select to place the chart as a new sheet. Finally, create a pdf file named SP3.pdf that includes the chart.
  4. Now check to see whether 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.) Write on the column next to where your ID appears the type of correlation you believe there appears to be. Use NEGATIVE, POSITIVE, NO CORRELATION to describe the correlation. Finally, create two PDF files named SP4-1.pdf and SP4-2.pdf - SP4-1.pdf will be the sheet with all of the data, including your assessment of the "type" of the correlation, and SP4-2.pdf will show the XY scatter plot that you have generated.

You will submit the files SP3.pdf, SP4-1.pdf and SP4-2.pdf as part of the ZIP file for this project.

Stage 3

Transfer the file in Stage3.xls to your PC and complete the following tasks using the downloaded file.

  1. 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. Then create a pdf file named SP5.pdf that displays the calculated values present in the Summaries worksheet.
  2. Create a pdf file named SP6.pdf that shows the value rules present in the Summaries worksheet. 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. After creating the file, go in and deselect that option so that the results are displayed again as they were before. Reformat column widths as needed.

You will submit the SP5.pdf and SP6.pdf files to us as part of the ZIP file for this project.

Stage 4

For this part of the project, you will need to go to the page at:
      http://lib.stat.cmu.edu/DASL/Datafiles/Hotdogs.html
and use the data on this page. You will be bringing the raw data into a Microsoft Excel spreadsheet, doing some mathematical and statistical evaluations of the data, creating graph based on this, and drawing what conclusions you feel you can from the information you have accumulated.

If your ISP is blocked from accessing this site, you might need to access the site from on-campus or connect to the campus VPN before having access to the site. You can download the campus VPN software from:
      http://www.helpdesk.umd.edu/topics/applications/vpn/
and use the "Tunnel-All" option.

  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. Create a pdf file named SP7.pdf for the full worksheet.
  6. 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. Write your guess on the first row of the sheet (use NEGATIVE, POSITIVE, NO CORRELATION to describe the correlation). Then calculate the actual correlation between the two variables on the same sheet and leave the result on the second row of the sheet.
  7. Create a pdf file named SP8.pdf that includes the XY plot, your guess, and the actual correlation.

You will submit the SP7.pdf and SP8.pdf files to us as part of the ZIP file for this project.

Stage 5

Given the following vote tallies, use Excel to determine the average vote, the standard deviation of the votes, and the confidence interval around the average vote. 

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

You will need to use the technique of copying a value into many cells in a column shown in class to create a new spreadsheet which has the individual votes in it. In other words if there were 20 votes of 10 and 7 votes of 9, there should be a single column with 20 cells that have 10's in them and a different column with 7 cells that have 9's in them.

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

In column M, place the appropriate functions in the cells so that the indicated values are calculated. For the confidence interval, use a 95% confidence interval.

Using the sheet contents, create a pdf file that shows the count, mean, standard deviation, and confidence interval. Name the file SP9.pdf .

What to Turn In

  1. Create a zip file with the following files:
           SP1.pdf, SP2.pdf, SP3.pdf, SP4-1.pdf, SP4-2.pdf, SP5.pdf, SP6.pdf, SP7.pdf, SP8.pdf, and SP9.pdf
  2. Once you have your zip archive file created and ready to submit, go to http://submit.cs.umd.edu and submit the zip archive file under the project submission area named "Project 6" there. You will need to log into that site with your University of Maryland DirectoryID and password. When you log in, you will see in the list of projects, one named "Project 6". When you click on submit for this project, you will be taken to a web page where you can Browse... for the file on your hard drive that you need to submit and then click the Submit project button to have it uploaded. It will take a few minutes to complete the upload (the exact time will vary based on your Internet connection) so be sure to wait and see that it has completed before going off to do something else. There is no automated grading for projects in this class, so please disregard the comments at the top of the screen related to automated testing of submissions. We will only grade the last zip archive uploaded.

Web Accessibility