Math 121 Calculus for Biology Spring Semester, 2007 Lab Help	Jan-07
	San Diego State University

This is your second computer lab. You must select a different lab partner for this lab. As with most weeks, you will be graphing functions, so you will continue to improve your Excel skills. This lab will extend your skills with Excel, particularly using the trendline feature.

Question 1 (B2): 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. Hopefully you copied your particular lab into a Word document. Go to the table of concentrations and absorbances and copy these numbers.
2. Open a new Excel file and paste this table into the spreadsheet.
3. Use Chart Wizard to create a graph of the data. Select XY-Scatter with the default option of points (the first box already highlighted).
4. Follow the directions to making the graph, 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, you click (single) on the data points. (You can right click to get options directly.) Once the data points appear highlighted, you return to the Chart on the Main Menu and select Add Trendline.
6. Under Trendline, you stay with the default, Linear. Next you select the Options folder in this window and check the box to Display the equation on chart.
7. 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) .
8. 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. Write your results in your Word document.

Question 2 (C3): This problem is similar to the material in the lecture notes in the Function Review and Quadratics section on the synthesis of mRNA.You will probably want to reread those notes to help you understand this problem.The first part of this problem is similar to the problems you did last time using Trendline, except you must set the y-intercept equal to zero.

In Part b, you should use the the graphing template of Excel. Change the first and the last values for the column of the independent variable (r) according to the range required. In this way you will get enough points within the range desired to graph a proper parabola. I want you to plot two "different" graphs on the same chart. First: evaluate the J(r) function as J= e₁² + e₂²+ e₃²+ e₄² with each e_i = g(P_i) - rP_i from the data table. This should give you the values for the second column. Then, on a third column you should type the expanded and simplified expression of J(r). This means, your final expression should be as the general form of the parabola: J(r) = ar² + br + c. In this way you may check that your general formula matches the correct parabola. When plotting the graphs with Excel, select the three columns at once, and choose the XY-scatter with the line-dot option. Please show both types of expressions in the legend of the figure. You should choose different colors, dots sizes and shapes for each parabola to differentiate them. If your general expression is correct, both curves should be one on top of the other. This means that the two parabolas are exactly the same.

Use the general formula of the parabola to find the r-coordinate of the vertex for Part c.