Laboratory Help Page

This page is designed to provide helpful information about the laboratory questions. You will find more details in the Laboratory manual that accompanies this course. Begin this lab and every lab by introducing yourself to your partner. Determine the times when you can meet together during the week before the lab is due at your next Lab session.

You should start this lab and each lab by typing the name of each team member and your group number on the Cover Page (or a copy of it). For general rules of what is expected in the Lab, read the Laboratory Guidelines. There is a Lab Manual that accompanies this course, which has many detailed worked examples from the labs for this course. Unfortunately, it is based on Excel and Word 2003, so it is not suited to the GMCS Computer Labs. Those of you with these better versions of Excel and Word can use this Lab Manual to help you at home with your assignments. This semester I do hope to make revisions to the Lab Manual and include steps for Excel and Word 2010. Initially, there is a link to a document for producing good graphs in Excel 2010. Also, there is additional documentation on what is expected through the link to the Good Graph Document.

Your Computer Lab contains two parts: 1. WeBWorKquestions answered individually (~60%). 2. A written report with graphs from one student's lab and well-written paragraphs from questions given in WeBWorK (~40%). The WeBWorK format for these Computer Labs should reduce the paperwork that you turn in. This WeBWorK Lab has four questions. The first problem asks questions about this help page and should help you work through the Lab more smoothly. The other problems are the main Lab questions. You answer many of the questions directly into your WeBWorK Lab individually. You will notice that several parts of the questions start with the phrase "In your lab report." For these parts of the questions you and your partner will need to create the appropriate graphs in Excel and write the appropriate sentences and paragraphs to insert into a Word document that you will turn in. Details on what is expected for your graphs can be found in the Laboratory Guidelines.

Problem 1: You follow the directions in the lab and according to the Lab guidelines to create a good graph of the two intersecting lines. This problem begins by having you generate the graphs of two lines. In the top row you insert labels x, y1, and y2 for the x values and the two lines. In the first column below x, you insert the integers from -10 to 10. (We will demonstrate the pull down feature in Excel in the Lab.) Next you name the x variable by highlighting the first column, going to Formulas on the Main menu, followed by Create from Selection, then accepting the default of Top Row. (Alternately, you can use the submenus labeled Define Name or the Name Manager.) In Columns B and C starting with B2 and C2, you type in your equations for the lines, typing " = m*x + b" where m is the slope of your line and b is the y-intercept. Next you pull down the equation to get the points on your lines.

After you have these data entries for the lines, you highlight the three columns and invoke Chart Wizard. To create a graph in Excel, you go to the Main Menu and select Insert. You will always be selecting the Scatter option, and in this case use the line sub-Chart. When you click on your graph, you will see Chart Tools in the Main Menu. Under Layout you will follow the directions to add Titles, Axis labels, and Gridlines. Select the option to not have a Legend for this graph, but rather use the Text Box option to create labels that you put right next to the particular lines.

Under the Layout there is an Axes label. You will always go to this box to adjust the Primary Horizontal Axis. At the bottom of the box are More ... Options, which allow changing the domain of your function. Less often, but in this problem you do change the Primary Vertical Axis. The details of this process to create a good looking graph will be gone over in your Lab section.You may want to follow the directions in the Sample Lab or in the special Excel Help section.

In the second part of this question, you'll be using features in Word to create a good looking equation. This is done by going to Insert on the Main Menu. On the right side of the page you will see Equation, which when clicked upon gives you the option to Insert New Equation. Many symbols and details for creating a good equation appear, which can be clicked upon to create a good equation in your Word document.

Problem 2: This problem introduces you to using the Graphing template that is provided. Details of using the Graphing template will be covered in Lab and are detailed on the special help page. This time you create the graph in Excel, then repeat the procedure from Question 1 to obtain a good looking graph.

Problem 3: You and your partner will want to have a good watch and cooperate on collecting the data on the number of chirps by each of the crickets that are listed in the WeBWorK problem. It is recommended that you bring earphones to hear only your crickets.(Information is available for hearing sound in the Lab.) Use the models from the lecture notes to obtain the appropriate temperatures. For the graph, you can once again use the graphing template for drawing the models from their formulae (or any other way you may have learned to draw straight lines in Excel). Next you enter your data on the spreadsheet. Return to the graph and right click on the graph. From the options, use Select Data ... and choose Add. You will have a new window, where you click on Series Name and type in an appropriate name, such as "Cricket Data." Next you click in the box for Series X values, then you highlight the data you want to add for the x-values. You repeat this process for the box labeled Series Y values, where you highlight the data you want to add for the y-values. Since the cricket data are data points, we need to change the line format to Marker format. You want to right click on a data point. Now you select Format Data Series ... Select Marker Options, then Built-in to choose the marker type and size. Finally, you need to remove the line format by choosing Line Color and selecting No line. Most of the calculations can be done either by hand or on the Excel spreadsheet. At all stages of the development of your lab, you should practice writing good, clear sentences (including spelling, with which Word should help you).

Problem 4: This problem is a study of absorbance versus the concentration of urea. Spectrophotometers are standard laboratory equipment and an example of a linear relationship. This lab introduces you to using the Trendline feature of Excel, and supplements the lecture notes for least squares fit to data. Below is a list of steps for solving this problem:

1. The easiest way in WeBWorK to make an accurate copy of your table in Excel is to first create a hardcopy of your Lab (PDF copy).
2. Highlight the numbers in the table of the hardcopy of the Lab and copy this into a new Excel file. From the Excel main menu select Data, then choose the item labeled Text to Columns. This will pop up a box where you can select the choice of delimited, then after the button Next you check the box for Space. When you finish this, you should have nice columns of accurately copied data with which to work.
3. To create a graph, you start by highlighting the appropriate data. From the Main Menu you select Insert and choose the option Scatter. When you press the Scatter option, five graph types appear, and you choose the one for data points, since our problem is from collected data.
4. Follow the directions for making the graph as you have before, giving it a title, labeling the axes, and giving the x-axis gridlines. Since there is only one graph you should probably remove the labeling option.
5. After finishing this initial phase, there are two good methods of getting the best fitting linear model. After clicking on the graph, the most straight forward technique is to go to the Main Menu under Chart Tools and click on Layout. From there you find the tab for Trendline. Under this tab you select the More Trendline Options and a box appears with Format Trendline. Alternately, you can simply right click on the data points and select the option to Add Trendline ... , then the box appears with Format Trendline.
6. In the box Format Trendline, you check the option Linear and the box to Display Equation on Chart.
7. To get more accuracy to match the answers in WeBWorK, you will want more significant figures. The easiest way to do this is right click on the formula box in your graph. Select Format Trendline Label and the default box says General. In this box you select Scientific, using at least 4 Decimal places. (This will give the equation formula with 5 significant figures.) This must be done before you modify anything in the formula box or Excel will not allow the formula to change.
8. Move this equation to a more visible part of the graph and make any other touch-ups to make the graph look good. For example, you should click on the equation and change the y to A (for absorbance) and x to U (for Urea concentration) .
9. Finally, paste this graph in your Word document.

Many of the remaining questions are handled by hand calculations with the information that you have acquired from the Trendline you just created. However, in Part c. you will want to take advantage of the Excel spreadsheet capabilities to compute the sum of square errors. Take your data set (assuming urea concentration in Column A and Absorbance in Column B), then in Column C use the equation from Trendlineto create the predicted values with the urea values from Column A. In Column D, you find the square error between Columns B and C. At the bottom of Column D, you can let Excel sum all the square errors in Column D to obtain your result. For more details on how to calculate absolute error and percent error read about errors in the Least Squares section of the notes (or the PDF version of the notes). We note that percent error is defined differently by different people, and we will be using the definition of signed percent error, so do NOT use the absolute value.