Analyze enzyme lab data using Microsoft Excel
!! only for reference, the data sets presented here are just an example.
▲Open Microsoft Excel.
▲On the bottom left corner, there are “sheet 1, sheet 2, and sheet 3”, you can rename the sheets as “substrate, inhibitor, pH”.
▲Enter your data into each of the corresponding sheet. In the first column (column A), enter time as minutes (0.5, 1.0, 1.5, etc..) in other columns, enter your OD460nm data. Example:
▲On the menu, choose insert→ charts→ scatter, choose scatter with only markers.
▲The graph should appear, if not, then click “select data” on the upper left and select your data range in the data sheet.
▲Double click on the chart, the chart tools bar should appear on the top, choose “layout 3” from the chart layouts.
▲There are lots of gridlines on the graph, you can leave them there. If you want to delete the gridlines, simply click on the gridlines and press backspace.
(▲Anther way to do is to add the trendline one by one, just click on one set of data on the graph and right click, from the popup menu, select “add trendline”.)
▲Direct left click on each of the trendline and then right click, on the popup menu, select “format trendline”, on the popped up “format trendline” window, check “set intercept=0.0”, “display equation on chart”, and “display R-squared value on chart”, under “Forecast”, change the “backward” value from 0.0 to 0.5, so that the line will start from the origin point. Also on the left side of the “format trendline” window, you can change the line color and line style so that you can clearly distinguish each line after you print out. If you have color printer, you can just change the color, if you only have black and white printer, you have to change the line style. When you do this step, you can drag the equation and R-squared value to and empty space and add the line number before the equation, so that the equations will not clump together. In the trendline name panel, click custom to change the name on the legend, for substrate, change to S1-S7, for inhibitor, change to I1-I8, for pH, change into pH2, pH4, etc..
▲R2 is a statistic that will give some information about the goodness of a model. In regression, the R2 coefficient of determination is a statistical measure of how well the regression line approximates the real data points. An R2 of close to 1.0 indicates that the regression line perfectly fits the data. If the line of your sample has a R2 number that is far away from 1 or even negative value, that means your data contain mistakes, you need to delete some data from the back of each data set, until the R2 value is at least 0.7-1.0, but you have to remain at least two data in each data set. If only two data left in one set and the R2 value still could not fall between 0.7 and 1.0, then just stop and leave two data there.
▲Change the name of horizontal and vertical axis by just click the Axis Title on the graph and enter the names, don’t forget the units (if any). If the axis title does not show, simply click the graph, on top, under chart tools→layout→axis titles, you can add horizontal and vertical axis tile.
▲Do the same procedure as above to sheet2(inhibitor) and sheet3(pH).
▲Now at the bottom, insert new work sheet, you can name it as MM (short for Michaelis-Menten).
▲Put the substrate concentration in one column, the slope you got in the first sheet in one column, and convert the slope value into velocity. Do the same to the second sheet. Then choose insert→ charts→ scatter, delete unwanted data groups, add axis titles(do not forget to put units), add trendline (as Excel does not has too many regression models to choose, just simply use logrithmic in the trendline/Regression model panel). Use appropiate legend, you can delete any obvious outlier velues, an example is like below:
▲Add a new sheet and name it as LB (Lineweaver-Burke).
▲Put the number of 1/[S], 1/V[S], and 1/V[I] in three columns. Then choose insert→ charts→ scatter, then add trendlines, axis titles, and change legends just as described above. You can delete any obvious outlier velues. Don’t forget to put the equation and R-squared velue in this graph.
▲Add a new sheet and name it as V vs pH. Put the pH value in one column, the slope you got in the third sheet in one column, and convert the slope value into velocity. Then choose insert→ charts→ scatter→ scatter with smooth line and markers. Delete unwanted data groups, add axis titles(do not forget to put units)
▲To change the legend text in Excel 2007 (the default text is “series 1, series 2, etc..”) you click on the legend. Now click on the “Design” tab and then on “Select Data”. Now highlight the series you wish to change name and click on “edit” to change its name.
▲Now you can print out all the graphs, all your data tables, make some conclusions based on your analysis above, and finish the rest of the requirements for your lab report.
The end