Transcript LecturesPart12

Computational Biology, Part 12
Spreadsheet Basics I
Robert F. Murphy
Copyright  1996, 1999-2001.
All rights reserved.
Cells
A spreadsheet is a two-dimensional array of
cells. Each cell is uniquely identified by
the row and column at whose intersection it
lies. Most spreadsheets use letters to
specify columns and numbers to specify
rows. Thus cell C7 is in column C (the 3rd
column) and row 7.
 Cells can contain values or formulas.

Values

A value is a constant entered into a cell. A
value may be numeric or textual.
 Numeric
values include integers, real numbers
expressed as decimals, or real numbers
expressed in scientific notation.
 Examples:
“5”, “7.11”, “2e-6”
 Textual
values normally consist of one or more
“printable” characters.
 Examples:
“Mass”, “Created by R. Stuart”
Labels

A label is a textual value used to mark,
identify or clarify other cells.A label might
be a heading on top of a column of numbers
or an identifier beside an important value.
 Examples:
“Temperature=”, “Concentration”
Formulas

A formula is an entry in a cell that specifies
one or more calculations to be done to
create a value for that cell. Formulas
 must
be identified to the program as distinct
from textual values (normally by preceding
them with an = or +)
 may refer to other cells
 may use operators, such as * and ^
 may invoke functions provided by the
spreadsheet program
Functions
A function is something provided by the
spreadsheet that is replaced by a value
during evaluation of a formula (it “returns a
value”).
 A function may or may not require
arguments.
 Examples: SIN, AVERAGE, DATE

Operator Hierarchy

It is important to know the order in which
operators are evaluated in spreadsheet
formulas. This order is referred to as the
operator hierarchy. When parentheses are
not present, exponentiation is performed
first, followed by multiplication & division,
followed by addition and subtraction. When
operators of equal hierarchy are present,
they are evaluated from left to right.
Operator Hierarchy Examples
Assume:
A1=1,
A2=2,
A3=5,
A4=13
What is A1 + A2*A3?
11
What is A1 + A2*A3 + A4?
24
What is (A1 + A2)*(A3 + A4)?
54
What is A1*A4/A2*A3?
32.5
What is A1*A4/(A2*A3)?
1.3
What is 10^A2/10^A3?
0.001
What is 10^(A2/10)^A3?
10
Formats

Values (including results of formulas) may
be displayed in a variety of formats. For
numeric values, the precision controls how
many decimals places are displayed. In
Excel, the precision is set on the Number
tab after selecting Cells... under the Format
menu or using the Toolbar or buttons.
Relative vs. Absolute References
A central feature of spreadsheet programs is
the ability to automatically change cell
references when the contents of a cell are
copied to other cells. This allows a formula
to be entered once but evaluated for many
different cases.
 To illustrate this, we will generate a model
that calculates the fraction of an ionizable
group that is charged at various pH values

Simple model: Acid Dissociation

For the dissociation of a weak acid
HB  B- + H+
HB is referred to as the conjugate acid and
B- is referred to as the conjugate base
 The equilibrium equations are

H  B 
Keq 
 HB
 B 
pH  pK  log
 HB
Ionization equilibria for amino
acids
Need to consider which groups on an amino
acid can be protonated/unprotonated
 The carboxyl and amino groups that are
involved in peptide bonds have relatively
constant pKs of ~2 and ~9
 The side chain pKs vary considerably.
 Illustrations for Arg and Tyr follow.

Arg-
+
_
+
+
H2Arg+
_
HArg
_
+
+
H3Arg++
+
_Tyr--
HTyr-
_
_
H2Tyr
H3Tyr+
_
+
Example: An Ionizable Group
Task: Given a pKa and a pH calculate
fraction of base in unprotonated form
 Step 1: Enter pKa and pH as constant values
into two cells

Example: An Ionizable Group

Step 2: Enter formula
using references to
constants
[B] 10 pH

pK
10
[HB]
Example: An Ionizable Group

Note: The formula was made visible in the
spreadsheet by clicking the Formulas box
on the View tab after selecting
Preferences... under the Tools menu
Example: An Ionizable Group

Step 3: Convert to fraction of B
Example: An Ionizable Group

Switch to viewing results of formulas rather
than the formulas themselves
Example: An Ionizable Group
New Task: Calculate fraction of B for more
than one pH value
 Step 4: Rearrange cells so that each row can
be devoted to one pH value

Example: An Ionizable Group

Step 5: Enter a formula to generate a series
of increasing pH values
Example: An Ionizable Group

Step 6: Copy the formula from cell B7
down to cell B8
Example: An Ionizable Group

Note that the reference to the pH value (cell
A7) changed to A8 (which we wanted to
happen) but that the reference to the pKa
(cell B4) changed to B5 (which we didn’t)
Relative References
“Normally” a formula containing the names
of cells (called references to those cells) are
updated when that formula is copied to
another cell
 The row number is incremented by the
difference in row numbers between the
original location of the formula and the new
location
 The column number is incremented by the
difference in column numbers

Relative vs. Absolute References
Such a reference is termed a relative
reference because the reference is
implicitly relative to the current cell
 We may want to “fix” or “hold” a reference
so that it doesn’t change during copying a
formula
 This is termed an absolute reference and in
Excel is created by putting a dollar sign ($)
in front of the row or column number, or
both

Example: An Ionizable Group

Step 7: Change the reference to the pKa to
an absolute reference
Example: An Ionizable Group

Step 8: Copy the formula in cell C7 down
(note that the reference to B7 updates to B8)
Example: An Ionizable Group

Step 9: Copy the formulas in cells A8:C8
down (note that the references to A7, A8,
and B8 increment but $B$4 doesn’t)
Example: An Ionizable Group

Step 10: Switch back to viewing values
rather than formulas to see results
Names

To make it easier to read formulas
containing many cell references, some
spreadsheet programs allow the creation of
names for cells (like variable names in
programs)
Example: An Ionizable Group
New Task: Define absolute name for cell
containing pKa
 Step 11: Select cell B4 then Define Name

Example: An Ionizable Group

Note Excel has chosen a name based on the
label in the adjacent cell and that the default
is for the name to refer to the currently
selected cell
Example: An Ionizable Group

Step 12: Use Apply Name to replace all
references to B4 in the spreadsheet with the
new name
Example: An Ionizable Group

Note formulas now show the name
Example: An Ionizable Group
New Task: Define relative name for pH
 Step 13: Select cell A7 and Define Name

Example: An Ionizable Group

Note name now chosen based on label
above B7. Change reference from $A$7
(default is absolute for names) to $A7 (row
number is allow to be relative)
Example: An Ionizable Group

Step 14: Apply Name
Complex Models and Graphing

Before creating complex models, it is
important to think about what graphical or
tabular output is desired from the model.
The organization of the spreadsheet should
be optimized for this output. For example,
if graphing of [P] vs. t is desired, try to
place all values for t and [P] in consecutive
cells in adjacent rows or columns.
A Model with a Single Output

(Demonstration D2)
A Workaround for Graphing
A spreadsheet that calculates some desired
quantity (e.g., net charge) for a single value
of some independent variable (e.g., pH), can
be used for graphing by adding cells in
which various values of the dependent
variable are manually tabulated as the
independent variable is changed
 (Demonstration D3)

Expanding a Model for Graphing
A better approach to the same problem is to
make many copies of the original
spreadsheet (using copy and paste) and
enter a different value of the independent
variable in each copy. The results can be
collected for graphing using references.
 (Demonstration D4)

Grouping Constants
The best method is to redesign the original
sheet so that only one row (or column) is
needed for each value of the independent
variable. This allows using fill down for the
subsequent rows of the sheet.
 (Demonstration D5)

Assigned Reading For Next Class

Yeargers
 Chapter
1
 Section 2.1
 Chapter 3 through Section 3.4
 Chapter 4 through Section 4.2