Sheet1

Sheet2

Sheet3

UST 289 Fall 2016 Due November 16,2016 Quantitative Assignment 2

Quantitative Assignment 2 Only use these instructions with EXCEL. Other

spreadsheet programs might require you entering the formulas a bit differently.

In this assignment you will learn how to enter formulas into Excel and then graph the

data that results. You will then use your graphs to answer questions regarding the

relationship that you see in the data.

The Great Lakes are the largest fresh water source in the world. We are fortunate to

live on the shore of one of them. The lake provides us with a ready source of water for

our daily lives and for the businesses that are located here. It also provides a cooling

effect during hot summer days and a warming effect during the winter. It also can give

us a steady supply of both lake effect snow and lake effect rain as well.

In this assignment, you will look at the possible effect on the evaporation of water from 3

selected Great Lakes due to temperature of the water and the overlake air temperature.

The three lakes have been chosen due to their differences in depth. Lake Superior has

a depth of slightly over 482 feet, Lake Huron has a depth of slightly over 193 feet, and

Lake Erie has a depth of slightly over 62 feet.

I have provided you with a basic Excel spreadsheet with the base information that you

will need for the assignment. It is a file in the Quantitative Folder named “Data for

Quantitative 2”. Follow the directions and answer all of the questions at the end of this

assignment. Your 4 graphs and written answers should be all in one Word document.

You may submit your assignment via email or print the hard copy and turn in at class.

While the assignment has a due date of March 5 th , you are free to submit it early. I will

grade it and return it. As long as I receive the assignment no later than March 19 th

, you

will be able to accept the grade, or revise the assignment and resubmit for regarding by

March 5 th

.

CAUTION: BE SURE TO SAVE YOUR WORK FREQUENTLY. YOU WILL REDUCE

YOUR AGGRAVATION THAT CAN OCCUR WHEN SOMETHING HAPPENS TO

CLOSE EXCEL AS YOU WORK!!!!!

1. To begin, open the Excel spreadsheet named “Data for quantitative 2”. You will

begin by converting the temperatures from Celsius to Fahrenheit. The formula for

converting Celsius to Fahrenheit is F o =C

o *9/5+32.

We will need to enter the formula in the cell in Excel. We will begin with Lake Superior

and in cell C7 (which is next to the first temperature) we will enter the following. Type

=((B7*9)/5))+32. then “Enter”. (What you are doing is telling Excel to multiply the value

in B7 by 9, then divide that answer by 5 and then to add 32 to the result.) Once we

have entered the formula, then we can copy it down the column. Click back on cell C7.

Rest your cursor in the lower right hand corner of the cell until a +sign appears. Click

UST 289 Fall 2016 Due November 16,2016 Quantitative Assignment 2

and hold down your left mouse button and drag the information down to the bottom of

the data. This should result in the values appearing as you move down the column.

Repeat the above for each of the columns. Each formula will start with a new column

identifier for row 7. (i.e. for Lake Huron, the identifier would be D7 instead of B7). You

also can copy and paste the formula in the beginning row of each cell for water surface

and Overlake air temperature. Click on cell C7 and then click on “copy”. “Home” tab

and then click on the copy symbol or click with your right mouse button and then choose

“copy”. When the cell highlights and flashes then hold down the control (marked Ctrl)

key and then click on cell E7, G7,I7,K7, and M7. Release the control key. Then click

“paste”. The formula will be duplicated in the chosen cells. Click on cell E7. Notice that

the formula changed to correct the first position to D7. If you check the other cells, you

will find that the formula autocorrected for the changes in position. Once you have the

correct formula in all of the starting cells, then repeat what you did above to drag the

formula down the column.

2. Now that you have converted your temperatures to Fahrenheit, you can set up

the data to make your graphs. First, put the Lake names in the columns above the new

Fahrenheit data. Now you are ready to begin to copy the data that you will use for the

graph. Click on cell A6, hold down the left mouse key and drag down to 2004. This will

highlight that column. (NOTE: Do NOT go to the lower right corner, if you do, then all

cells will lose their value.) Now hold down the control key (Ctrl) and click on the cell that

reads Superior above the Fahrenheit temperatures. While holding down the control key

hold down your left mouse button and drag down to the bottom value. Then repeat this

for Huron and Erie data. Release the cntrl key. Then click copy. The highlighted cells

should be framed and blink. Scroll down below the data and click on a cell A46. Click

on the “Home” tab. Then click on the down arrow under the “paste” clipboard. Choose

the first option under “paste values” and click. This will copy your highlighted data.

Now you are ready to make the graph for “Water Surface Temperatures”.

3. Highlight the four columns containing the data. Go to the “insert” tab. Choose

the graphic for “Line” and then the first choice under “2D Line”. This will make your

graph appear. Click on the design tab and then click on “quick layout” to the left.

Choose the first option on the drop down menu. Then change “Chart Title” to “Water

Surface Temperatures” by clicking on the words and deleting them. Change “Axis Title”

to “Degrees Fahrenheit” in the same manner. You’ve now completed your first graph.

Move the graph under the data. (Click in the corner of the graph, you should see a 4

way arrow before you go to move the graph.)

4. Repeat the above instructions from 2 and 3 to transfer data and make a chart for

Average Overlake Air Temperature. Be sure to adjust labels as necessary. You now

will have 2 of your graphs done.

UST 289 Fall 2016 Due November 16,2016 Quantitative Assignment 2

5. Now you will compute the difference in Water Surface Temperatures and

Average Overlake Air Temperature. Working with the data you transferred to make

your graphs, first copy the years to column K. Copy the “Superior, Huron, and Erie”

headers over, beginning in column L one cell above the starting date. Now you can

compute the difference in Water Surface Temperature and the Average Overlake Air

Temperature. In cell L47 enter =B47-G47 and then “enter”. These cells should contain

the temperatures for Lake Superior. Click back on cell L47 and then drag the formula

under Huron and Erie. The cell numbers should change to reflect their respective

temperatures. When done hit “enter”. Go back to cell L47 and highlight the next two

cells as well. Then go to the right hand corner and when you get the + sign, left click

and drag the formulas down to the bottom. You now will have the differences computed

and following the directions in step 3, make a graph showing these differences. You

have now completed the 3 rd

graph for this assignment.

6. For your last graph, use the data supplied for Evaporation and graph the amount

over time. See step 3 for directions on graphing and labeling. You can use the data as

it is set up. Highlight it and begin the graphing process. Be sure to correct the chart

title to show what the graph is presenting to us.

7. Copy and paste your graphs into a Word Document and answer the following

questions.

A. In looking at your graphs for Water and Overlake Air Temperature, what do you

see? Are the graphs consistent with each other? Are there any unusual years? Which

lake has the highest temperatures, which lake has the lowest? What are some factors

that might affect water temperature?

B. In looking at your graph of the differences between the Water and Overlake Air

Temperature, what do you see? Are the differences consistent lake to lake? Which

lake has the highest difference? Which appears to have the least difference? Why do

you think this might occur? Is there an anomaly in the data? Which lake has this

anomaly if there is one? Is there something in the previous graphs that might have

alerted you to this if there is one?

C. In looking at your graph for Evaporation, what do you see? Which lake had the

highest amount of Evaporation? Which had the least? Would you expect this given the

temperature data that you graphed? What might be some other factors, other than

temperature that might cause this difference?