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 5th, 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 19th, you will be able to accept the grade, or revise the assignment and resubmit for regarding by March 5th.
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 Fo =Co*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 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.
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 3rd 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?
Attachment:- Quantitative_2_Data_for_Students.xls