CS130/230 Assignment 2

Graphing and Logic

 

Date Assigned: Monday, January 12, 2004

Date Due: Tuesday, January 13, 2004

Total Points: 60

 

Save or print a pdf version of this document

 

Add the following worksheets to the same Excel workbook you created for assignment 1. Do not delete the assignment 1 worksheets.

Worksheet 1 – name it “Climate Tracker”

The site http://cdiac.esd.ornl.gov/epubs/ndp019/ushcn_r3.html contains data on the monthly mean average temperature for various weather stations around the United States.

 

From this website, find the weather station that is nearest your hometown, and copy the monthly mean average temperature to an Excel spreadsheet. The format of the data is:

Station number – year- jan, - feb – mar – apr – may – jun – jul – aug – sep – oct – nov – dec – winter (dec-feb) – spring (mar – may)  - summer (jun – aug) – fall (sep – nov) – annual average temperature (jan – dec). All values are in degrees F and –99.99 indicates missing data. The number of years will depend on the data that you use. The data for Vancouver, Washington ranges from 1891 to 1994.

 

Copy this data into your worksheet. You will need to use the (Text to Columns) command in the (Data) menu to split the data into columns.

 

Once you have all your data in place, do the following:

-       Add a row at the end of the table that will contain the average temperature for each month for the whole period.

-       Add a row to store the maximum average temperature for each month.

-       Add a row to store the minimum average temperature for each month.

Insert a line chart into your worksheet for the first year in your data and the last year in your data. The x-axis will be the months of the year (make sure that these are labelled correctly) and the y_axis will be the temperature in Fahrenheit.

Worksheet 2 – name it “School Savings”

Your uncle runs a custom drapery shop. He’s decided to send his son to private school. He has job orders at his shop for the next six months: $800 in January, $750 in February, $550 in March, $665 in April, $388 in May, and $767 in June. Each month your uncle spends 40.25% of the orders on material, 3.5% on patterns, 3.25% on his retirement and 44% on food and clothing. The remaining profits will be put aside for his son’s education. Also, your aunt has agreed to provide an additional $25 whenever your uncle’s monthly profits exceed $50.

 

-       Create a worksheet that shows orders, expenses, profits and savings for the next six months, and totals for each category.

-       Goal seek to determine what percentage of profits to spend on food and clothing if $800 is needed for the school.

 

Everything in the worksheet should be right justified and formatted correctly (i.e. currency, percentage, etc) except the text that asks the user to enter in the values.

 

Add both these workshops to Excel workbook you created for your previous assignment and place it in the ‘CS130 Drop’ folder. Your workbook must be in the drop box by 9am for full credit.