bi335/notes/excel_help_worksheet4.htm

Help with EXCEL on Worksheet #4:

A. How to set up two curves for comparison

1.Set up a spreadsheet that has the top three rows reserved for labels and constants. You will be making two sets of constants Vmax1, Km1 and Vmax2 and Km2.

2. Put in starting values for Vmax and Km in each column.

3. Put in three columns just below that labelled S, v1 and v2.

4. Put in a range of values for S

5. The new Excel has a "name" feature that will really help in making this spreadsheet easier. First select cell B2, the value of Vmax in B2.

6. Next name this cell Vmax1. From the pull down menu Insert/Name/Define and you will get a dialog box like the one below, and type in Vmax1.

7. Do the same for Vmax2, Km1, Km2.  
8. Select the entire range of substrate concentrations (by clicking and dragging from A6:A17 in our example). Name this S  

9. Go to cell B6 to enter the first equation. By using this naming scheme, we can type in an equation using the constant and variable names. Type in

=Vmax1*S/(S+Km1)

and then hit enter. The value should be calculated to be 0.

 

10. Select cell B6 and, while still holding the mouse down, drag down to B17. Hit Ctrl D or use the pull-down menu Edit/fill/down.

11. Do the same for column C, using Vmax2 and Km2 instead. When you finish, you should have a sheet that looks like the one to the right.

12. Select the range of cells from A5:C17 by clicking and dragging.

13. Go the the pull down menu, Insert/Chart to get the chart wizard. When that comes up select the XY plot.

14. Hit the Next> button or Finish to creat a chart.

15. Your chart should look like the one to the right, and you should be able to make changes in cells that hold Vmax1, Vmax2, Km1 and Km2 and see the changes immediately in the graph. That is the purpose, for you to see the effects of changing Km or Vmax.

 

B. How to make a reciprocal plot

1. Adding on to the worksheet created above, make three columns that are labelled 1/S, 1/v1 and 1/v2.

2. Go to the top cell in this column and type

=1/A7

which will enter the value for 1/S

3. Fill down for all of the S values.

4. Do the same for 1/v1 and 1/v2.

 
5.Highlight the range of cells in the three columns and make an X-Y plot as you did in steps 13 and 14 in the previous section. You should get a plot that shows the double reciprocal plot and changes as you adjust Vmax or Km.  
   

C. Using these charts

The point of these exercises is to see how the parameters Km and Vmax shift the curves for enzyme kinetics. Questions 3 and 4 on worksheet #4 use these type charts.

D. Making a sigmoidal chart

You can modify the equations in the spreadsheet that you made in part A to compare sigmoidal curves for allosteric enzymes. Simply change the equation that you entered in Part A - step 9 to be:

=Vmax1*S^2/(S^2+Km1^2)

Make a similar change (but using Vmax2 and Km2) to the next column. Your graphs should look

   
   

10/3/98 John Rueter