Download Using Microsoft Excel to Analyze Kinetic Data: Setting Up, Sorting, and Plotting and more Lecture notes Statistics in PDF only on Docsity!
Figure 1
Using Microsoft ®Excel to Plot and Analyze Kinetic Data
Entering and Formatting Data
Open Excel. Set up the spreadsheet page (Sheet 1) so that anyone who reads it will understand the page (Figure 1).
- Type a title in the cell in the upper lefthand corner, cell A
- Label column A as the substrate concentration in cell A
- Label column B as the reaction rate for 30s in cell B
- Label column C as the reaction rate for 1min in cell C
- Adjust column widths to fit the labels by clicking on the column heading and dragging the border to the appropriate width
Enter your data pairs in the appropriate columns. (Don’t forget to enter 0,0 for one of your data pairs.) If your data was not collected in order of increasing substrate concentration, enter the data pairs in the order collected and sort them in ascending order (Fig. 2).
- Click and drag over the cells that contain the data pairs
- Choose Data > Sort
- On the Sort menu, select “Column A” in the drop down menu for Sort by
- Click “OK” (Fig. 2)
Figure 2
Once the data is sorted in ascending order, the reaction rate for 1min can be calculated in column C by entering the formula =(B42)* in cell C4. You can copy and paste the formula into the other cells in column C by clicking the right-hand button on the mouse and making the appropriate selection (Fig. 3) or by double- clicking and holding on the box in the lower right hand corner and dragging the mouse down the column for the length of the data.
For now, skip column D and label row 3 in columns E and F “1/S” and “1/v,” respectively. Calculate the values for these columns by taking the inverse of the values in column A and column C ( e.g. , skip formulas for values of “0,” so in cell E6 type =(1/A6) and in cell F6 type =(1/C6) ). Copy and paste the formulas into the other cells (Fig. 4).
If desired, the values for 1/S and 1/v can be formatted to three decimal places to make the sheet easier to read.
- Select the data in the two columns
- Right click and choose Format Cells
- Click on the Number tab
- Under Category , choose Number and set Decimal places to 3
- Click OK
Figure 3
Figure 4
On the Chart Layouts menu left click on the arrows to get the menu of choices. Select Layout 9 (Fig. 8)
This will format the plot to allow the easy addition of a title and axis labels. In addition, it will draw a regression line and provide the equation of the line and its R value. (Fig. 9)^2
Enter the chart title and axis titles by left clicking on the appropriate label (click 3 times) and typing in the information. The legend on the right is unnecessary and can be deleted (Fig. 10).
Figure 8
Figure 9
Figure 10
Figure 11
In the example shown above (Fig. 10), the R value indicates that almost 98% of the variation in 1/v (y) is due^2 to the variation in 1/S (x). In addition, if we take the square root of R we can determine that the correlation^2 coefficient, r, is almost 1, indicating an excellent fit between the data points and the regression line and showing that as 1/S increases, 1/v increases. The equation of the line is used to provide the K m and Vmax values for the enzyme. The y-intercept, 0.0076, is equal to 1/V max. Therefore, Vmax = 1/0.0076 = 131.579. The slope of the regression line, 0.7053, is equal to K /V m max , so Km = (Vmax )(K /Vm max ) = (131.579)(0.7053) = 92.803. These values can be calculated and recorded on the spreadsheet (Fig. 11).
The values for K m and Vmax provide valuable information about the enzyme and can be used to plot the Michaelis-Menton Curve.
- Create a new plot, showing the relationship between S and v.
- Highlight the data in columns A and C. Select the first value in A6 and drag down the data column. Holding the Ctrl key, left click and drag down the data in column C (Fig. 12)
- As you did earlier, select the Insert menu from the toolbar
- Select the same type of scatter plot as before. A plot will be generated automatically.
- Move the plot to a convenient location.
- Format the plot by selecting Layout 1 from the Charts Layout menu.
- Modify the title and axis labels; remove the legend (Fig. 13)
Figure 12
- On the Select Data Source menu (Fig. 16), left click on the Add button to get the Edit Series menu.
- Fill in the blanks on the Edit Series menu with the appropriate information (Fig. 17)
- In this example, the Series name was named “calculated.” Enter the data for the X and Y values by
Figure 15
Figure 16
Figure 17
clicking on the small boxes with the red arrows and selecting the appropriate data series from the worksheet.
- Click OK on the Edit Series and the Select Data Source menus and the data in the “calc v” column will be added to the plot (Fig. 18).
The data points from the calculated values need to be converted to a line.
- Move the cursor to a data point, leaving it still until a popup box appears that shows calculated as the series (Fig. 19)
- Right-click and choose Format Data Series from the menu
- On the Format Data Series submenu, select Marker Options and then None. Now, select Line Color and Solid line.
- Click Close to see the modified plot on the spreadsheet (Fig. 20).
Figure 18
Figure 19
After clicking on “OK” several tables and plots are generated on a new worksheet. The Regression Statistics table (Fig. 22) provides the R value, among other values. The^2 ANOVA table provides a Significance F value
which allows us to consider the significance of the regression model. In this instance, the value is < 0.05 so the model is significant. The third table lists the y-intercept and slope under Coefficients. The Residual Output and Probability Output tables list the values used in the Residual Plot and Normal Probability Plot (Fig. 23), respectively. The Residual Plot indicates if the residuals (difference between actual values and predicted values) are random or homoskedastic. If a pattern is displayed, for example the trumpet-like arrangement of points in this plot, the data are said to be heteroskedastic , suggesting that the residuals are somehow being influenced. At this point, one must determine if the linear regression model should be accepted. Since the linear regression was applied to a double-inverse plot, such a pattern is not unusual and the model is accepted. The normal probability plot indicates whether the residuals follow a normal distribution, in which case the points will follow a straight line. Expect some moderate scatter even with normal data. Look only for definite patterns like an "S-shaped" curve, which indicates that a transformation of the response may provide a better analysis. (from Design Expert 7.0 from Stat-Ease)
Figure 22
