V22.0004 Computers in Principle and Practice
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.
Create a web page called "assign7.html" and put a link to it on your
Then use the Excel (under FILE / SAVE AS WEB PAGE) to
make your pages, and make certain that they include
your name, section, and other identifying information.
Your "assign7.html" page should
have links to all three of the pages published.
You will need to publish
three pages as follows: one with the data visible; one with the formulas visible ; and the graph.
First, publish your page showing the values.
Then, publish it again showing the formulas. This can be done as follows: Under "Tools," "Options," (or "Preferences"
) with the "View" tab, click on "Formulas" under the "Window Options" so that when you publish the sheet the formulas will be displayed and
not the values.
- To make the chart, note that you will need to copy the years into a column adjacent to the required data for the purpose of setting up the chart (and then hide that column before printing).
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
Here are the email addresses for assignment submission for the various sections: