Analysing Data Aims: ™

Analysing Data
™ to know how to plot simple graphs and those with logarithmic scales
™ to know about relative and absolute references
™ to know how to copy graphs from Excel to Word
™ to use graphs to test hypotheses
™ to use Excel to estimate parameters
™ to be able to plot simple and transformed data,
™ to be able to use the solver to estimate parameters
We shall use Excel to plot a graph of membrane voltage against potassium concentration for the
squid giant axon. The Nernst equation is used to predict the expected voltage from the potassium
E =
[ K
[ K
Begin by double clicking on the Excel icon and then typing the data into the first two columns. Go
down the column by pressing the Enter key. Move across square to square or up by pressing the
arrow keys.
Kout, mM
Vm, mV
Fig 1: Excel sheet indicating the toolbars (chart wizard
marked) and formula bar.
If you make a mistake: press Ctrl+Z to undo (Ctrl+Y to redo), or click in the Formula bar to edit
To plot the graph, First, select all the numbers (point the mouse at the top left, then drag down and
right with the button held down). Next, double-click the chart wizard button. The cursor changes
to a cross, and you can set the size of the graph you will make by pointing the mouse to the upper
left corner, holding down the mouse and dragging.
Formatting your work: This is the next important bit. You really need to get a title (e.g. test of
Nernst Equation) and nice scales. Double click the picture to edit it and make changes in appearance.
Some things, like the title text can be changed after a single click on the title and pointing the mouse
at the offending text. Other things like adding axis labels can be done by clicking on the menu bar
and choosing insert. Once things are present, their appearance can be changed by double clicking and
analyseing.doc- 1 - printed at 01/10/03 12:11: page 1
selecting the new appearance. For example, double click on the axis and choose marker types, line
styles and fonts.
Fonts: the size of the font is measured in points, with about 70 points /inch (height). For a printed
page, 10 or 12 point is OK. For a poster, use 18-36 point so it can be read from a distance. For this
kind of graph, I suggest 14-16 point sizes.
Logarithms: The Nernst Equation is a logarithmic equation:
[K ]
⋅ ln [ K ]oi ,
so we really need to get a logarithmic scale on the X axis. Double click the X axis and choose scale,
then click the logarithmic box with an X.
Adding the expected values: At Kout = 160 mM, the voltage was 0, so that the internal potassium
concentration must also be 160mM. The Nernst equation here then be evaluated to
E = 56 log( Kout / Kin ) = 56 log ( Kout /160 )
Enter the heading Vexp into square C1 of the worksheet. Fill the rest of the column with values from
the formula, by typing = 56 * log(A2/160) into square C2. Note that there must be no gap between
the log and the (. This is a formula, relating the value in C2 to A2. You could type formula into the
rest of the squares, but it is much easier to get the spreadsheet to do it for you: move the mouse over
the tiny square in the bottom right hand corner. When the cursor changes to a thin cross, press the
mouse button and drag down. The cells will fill with numbers determined by formula but the formula
will be relative. Thus cell C3 will contain a reference to A3, C4 to square A4 and so on.
Hint to help you check the worksheet: square C2 will contain -140.288 and square C10 0.
Now add the new data to your worksheet. Select column A (including the title) and then hold down
the Ctrl key and select column C. Choose Edit | Copy and then highlight the chart and use Edit
|Paste special to add the extra data to the experimental values. Caution: make sure (i) you use Paste
special and not the normal paste and (ii) you check the Categories (X-values) in first column box.
What is the difference between experimental and predicted data?
Now double click one of the points and use the
Patterns dialog box to format the Nernst expected
line as a joined up line with no points (called
markers). Equally, I suggest that the observed
points be formatted as markers with no lines. My
current version is like this, but you can do better!
You get arrows from the drawing toolbar and can
Nernst expected
make them point where you like. [The drawing
Vm, mV
Vexp, mV
toolbar can be displayed by choosing View |
Toolbars and clicking on the drawing toolbar in
the dialog box.] You can add text by clicking once on a bit of the chart which is empty and then
typing; the text will appear in the formula bar. Move the text by clicking once and then using the
handles which appear around it.
We can really say that the data deviate from the Nernst equation: hooray!
analyseing.doc- 2 - printed at 01/10/03 12:11: page 2
Try a second formula for the membrane potential (the Goldman Equation)
 P ∗ [ K o ] + PNa ∗ [ Na o ] 
∗ ln K
i 
 K
where the P values are the relative permeability.
This can be rearranged to give
 [ K ] + α ∗ [ Na o ] 
 where α is the ratio of permeabilities.
∗ ln o
The inside and outside concentrations of Na are 50 and 440 mM. As before, take Ki as 160 mM and
RT/zF as 56 when using logs to base 10. Plot a graph of the expected data. You will need to know
the value of α. Measured values suggest that PNa is 1/100 times that for PK, so use a value of 0.01 for
α. It is helpful for next time if you put α in a square on your worksheet and use absolute reference to
the values of α; e.g. if the value of α is in square L2, refer to it as $L$2.
Would another value for α do better? Try it and see if the curve looks better.
Estimate it: We can estimate parameter(s) using the solver function in Excel.
What we will do is to allow Excel to change the value of the square containing the permeability
(which we called α) until we get a good fit between the data and the theoretical curve. However, we
need some criterion for a good fit:
Minimise Least squares method: the easiest way is to minimise the total least squares deviation., so
that we calculate the deviation between theory and data for each point, square it and add them up.
Then we want to make this sum as small as possible.
™ Calculate the
™ deviation between the data and the Goldman expectation in the next column
™ sum the squares using the =SUMSQ(E2:E10) formula (if your deviations are in E2:E10(!!)
Label the square (sum sq or similar)
™ open Tools | Solver - if Solver is not on the tools menu, use Tools | Add-Ins to activate it
™ use the solver dialog to minimise the sum at the foot of the column by changing the contents of
the square containing the value of α.
™ use a constraint if necessary: α must be greater than zero, (negative values of α are not possible
for a membrane permeability). See Figure (below) for an example.
analyseing.doc- 3 - printed at 01/10/03 12:11: page 3
In my case, this gives a much better fit than the original values, suggesting that the value of
permeability had been over estimated by a factor of 5. Plot the graph to see how much better it is.
Can you improve the original curve by changing the estimated Na+ concentration instead of the
membrane permeability? How does the effect of limiting the Na+ out concentration (e.g. above
20mM) matter.
Reference: see Hodgkin & Horowitz, (1959) J Physiol 148: 127-160
Receptor binding examples: Scatchard and Hill plots can be used to estimate the binding properties
of an agonist (e.g. acetylcholine, octopamine) on nervous tissue. As more agonist is added, more
binds to the receptors, until they are all saturated. The maximum bound level is usually called Bmax.
The properties of the receptor may also be characterised by Kd, the concentration of free agonist
(ligand) when half the total receptor binds agonist molecules. Each receptor molecule may bind one
or more agonist molecules. In this example, the amount of agonist (octopamine) free in the solution
was set and the proportion which bound specifically to the receptors in the CNS was determined.
™ Plot a scatter graph of these data and label the axes.
Does it look like a straight line?
™ For the Scatchard plot, plot a second graph with the
Bound amount along the X axis and the ratio
Bound/Free along the Y axis (Hint: use a formula to
make the calculations!)
™ What are the Y-axis units?
™ Add a Trendline, by right clicking the data points and
choosing Add Trendline
™ Display the equation by right clicking the Trendline
and choosing Options
free (nM)
Bound (fmol /g)
Estimates version 1: The binding parameters can be
estimated from the Scatchard plot,
™ if the line bends, then it indicates co-operative
binding of the receptors
™ the maximum binding Bmax is indicated by the place
where the line crosses the X-axis, (Use Trendline |
Options and set it to go Forward 1000 units)
™ and the Kd from minus the reciprocal of the slope (slope is –Affinity constant, KA, = 1/Kd); Kd = 1/m where the trendline equation is y = mx+c
Estimates version 2: A better way to estimate the parameters is to fit the equation
B = Bmax •
Kd + F
and to minimise the error using least squares method. (Sometimes this is called non-linear regression)
™ Expand the axes on your Scatchard plot and estimate the Kd and Bmax by eye.
™ Copy your original data to a new sheet in the workbook and put your first estimates in a
convenient square.
™ Use a formula to calculate the expected Bound concentration and calculate a column of
analyseing.doc- 4 - printed at 01/10/03 12:11: page 4
™ Sum the squares of the column by inserting the =sumsq formula at the bottom.
™ Minimise the summed least squares error using the Solver and write down the values of Bmax and
Kd .
™ Use your values of Bmax and Kd to add the line of best fit to the original graph you had of free /
™ How much better is the fit?
Write a brief report in Word and insert the graphs to show your output.
References: (explains derivation of
curve in pages #Receptor-Ligand Kinetics, Single-Binding Site and Classical Scatchard Plot (shows another example comparing the
linear and non-linear fits)
C. J. H. Elliott, October 1, 2003
Self Assessment
You may like to complete this table for you own file:
Things I found easy
Things I found hard to
Things that seem very
analyseing.doc- 5 - printed at 01/10/03 12:11: page 5