V22.0004 Computers in Principle and Practice
Fall, 2006

Assignment # 7 (Excel #1 )

1. You will be creating a Microsoft Excel Spreadsheet that contains actual data from a NASA weather site with data from around the world reflecting average monthly temperatures. We will work with data recorded at the Central Park Weather station, here in New York City. (A graphical view of the data is available for those who find this of interest as well as a copy of the dataset in the event that that site is not available.)

First, copy and paste the data into a new worksheet and use Data / Text to columns to parse the data into the appropriate columns and format the sheet based on the model below. Remove any unnecessary columns and then write the formulas that you will need to complete all of the information shown in the file below for the years 1881 - 2003. Use Excel's copy tools to replicate the formulas quickly and pay close attention to relative vs. absolute addressing to make the copying easier. Notice that there is no data available for the month of February, 1991 so you will need to account for that in your spreadsheet by using the =NA() function. Be sure to round the results of the calculations to one place after the decimal. In addition, the data source listed is a hyperlink. For the rightmost column, here is the formula to convert a temperature from Celsius to Fahrenheit:

F = (C * (9/5)) + 32 where F is the temperature in Fahrenheit and C is the temperature in Celsius.

Here is the top of the spreadsheet; be sure to include your name and section in the Title:

2. Next set up a chart of the average, high, and low temperatures for each year from 1993 - 2003, as per the following example. Be sure that your name and section are listed in the title of the chart.



3. Publish your spreadsheet on the World Wide Web through your i5 account.

3. 6) Finally, email the grading account for your section to tell us that your page is ready for grading. The subject of your message should be "Assignment 7 " and the body should include both your name and the HTML address in the form:


Here are the email addresses for assignment submission for the various sections: