Unformatted text preview: Computational Biology, Part 16
Circular References and
Recursion Relations
Robert F. Murphy
Copyright © 1996, 19992006.
Copyright
All rights reserved. Recalculation
s When a cell value is changed, what
When
determines whether other cells are
recalculated? There are two options:
recalculated?
x Automatic
3 The program recalculates every cell (if necessary)
The
every time a cell changes
every
3 This is the default for most spreadsheet programs
x Manual
3 The program waits for a user command before
The
recalculating
recalculating Recalculation order
s When cells refer to each other, what
When
determines the order in which the
calculations are done?
calculations
s Some (older) spreadsheets calculate cell
Some
values in a set order.
values
x Columnwise
3 Top to bottom for 1st column then 2nd column...
3 A1 to An, B1 to Bn, C1 to Cn, etc.
x Rowwise
3 Left to right for 1st row then 2nd row... Forward References
s A formula that refers to a cell “ahead” in the
formula
recalculation order of the cell containing the
formula is called a forward reference.
forward
s If the recalculation order is columnwise,
If
columnwise
references to cells to the right of the cell
right
containing the formula are forward
references.
references.
s If the recalculation order is rowwise,
If
rowwise
references to cells below the cell containing
below
the formula are forward references.
the Example of forward reference
s Given the following formulas, are there any
Given
forward references if the recalculation order
is rowwise?
rowwise Example of forward reference
s Given the following formulas, are there any
Given
forward references if the recalculation order
is rowwise?
rowwise s Yes, cell B2 has a forward reference to A3. Example of forward reference?
s Given the following formulas, are there any
Given
forward references if the recalculation order
is columnwise?
columnwise Example of forward reference?
s Given the following formulas, are there any
Given
forward references if the recalculation order
is columnwise?
columnwise s No, the results are Dynamic recalculation
s Modern spreadsheets do not recalculate in
Modern
any set order. They dynamically determine
dynamically
which cells need to be updated.
which Circular references
s A formula that refers to a cell containing a
formula
reference to the original cell is called a
circular reference.
circular
s Circular references can only be resolved by
Circular
iteration, following the recalculation order.
iteration
s Circular references may either converge or
Circular
converge
diverge, depending on whether they
diverge depending
approach a limiting value or not.
approach Example of circular references
s The following cells contain circular
The
references. Are they convergent or
divergent?
divergent? Enabling iterations
s If we enter these formulas into cells A1 and
If
B1, the program will give an error message
B1, s In order to use a spreadsheet with circular
In
references, we need to set the program to
use iterations to attempt to resolve the
references.
references. Enabling iterations
s For Excel, select Preferences from Excel
For
menu and select the Calculation button.
Calculation Enabling iterations
s Check the Iteration box and leave the
Check
Iteration
default of 100 for Maximum Iterations.
Maximum
Note that calculation options are saved
along with the spreadsheet and set when
loading a spreadsheet IF IT IS THE FIRST
ONE LOADED.
ONE
s The program should begin calculating
The
values, ending with a number very close to
1 in cell A1 and a number very close to 2 in
cell B1.
cell Convergent circular references
s Here is a graph of the values in the cells
Here
versus iteration number.
versus
Convergent Circular References
2 A1=B1/2 1 B1=A1+1 Cell Value 0 1 4 7 10 13 16 19 22 25 28
Iteration Another example
s Are the following circular references
Are
convergent or divergent?
convergent Divergent circular references
s Here is a graph of the values in the cells
Here
versus iteration number.
versus
Divergent Circular References 30000 20000 A1=B1*2
B1=A1+1 Cell Value
10000 0 1 5 9 13
Iteration 17 21 25 29 Self references and initial values
s Cells can make circular references to
Cells
themselves (called self references).
self
s Excel assumes a value of zero for a cell
Excel
zero
before the first iteration of a self reference
(or a circular reference to a cell that has not
yet been evaluated).
yet
s Thus, entering =A1+1 into cell A1 will
Thus,
result in the value 100 (assuming maximum
iterations is set to 100).
iterations Using manual recalculation for
circular references
s In a complicated model involving circular
In
references, it is often useful to turn off
automatic recalculation so that you can
control when recalculation is done (e.g.,
after all formulas have been entered).
Recalculation can be initiated using =. Summary: Recalculation
s Recalculation Timing
x Automatic or Manual
Manual s Recalculation Order
x Columnwise, Rowwise or Dynamic
Rowwise Dynamic s Circular References
x Resolved by iteration
Resolved
iteration
x Convergent or divergent
divergent Goal Seek
s Varies one cell in order to have another cell
Varies
reach a particular value
reach
s Uses a nonlinear fitting method with
Uses
empirical derivatives
empirical Recursion Relations Illustration from Population
Dynamics (after Segel)
s Consider a species of insect that hatches in
Consider
the spring, lays eggs in the fall and dies in
the winter.
the
s Let Ni be the number of insects in year i.
Let
s It is safe to say that the number of insects in
It
a generation will be a function of the
number in the previous generation, that is,
Ni+1 = f(Ni). A recursion relation
This type of equation, Ni+1 = f(Ni), is called
This
is
a recursion relation. Given N0 and f we
recursion
Given
can determine all values of N.
s In the simplest case, consider the birthrate
In
to be constant. Let R be the ratio of the
birthrate in the next generation to the
birthrate in the current generation.
birthrate
s The simplest case
s Then f(Ni) = RNi.
Then s Quite naturally, the behavior of Ni depends
Quite
on R.
x R<1 ⇒ Ni → ?
<1 The simplest case
s Then f(Ni) = RNi.
Then s Quite naturally, the behavior of Ni depends
Quite
on R.
x R<1 ⇒ Ni → 0
<1
x R=1
=1 ⇒ Ni → ? The simplest case
s Then f(Ni) = RNi.
Then s Quite naturally, the behavior of Ni depends
Quite
on R.
x R<1 ⇒ Ni → 0
<1
x R=1
=1 ⇒ Ni → N0 x R>1
>1 ⇒ Ni → ? The simplest case
s Then f(Ni) = RNi.
Then s Quite naturally, the behavior of Ni depends
Quite
on R.
x R<1 ⇒ Ni → 0
<1
x R=1
=1 ⇒ Ni → N0 x R>1
>1 ⇒ Ni → ∞ A better estimate
s Unlimited growth is unrealistic; eventually
Unlimited
something (e.g., food supply) will limit
growth.
growth.
s Assume R changes with Ni. Assume it
Assume
Assume
decreases linearly as Ni increases
x R(Ni) = r[1Ni/K]
s Then
x Ni+1 = rNi[1Ni/K] with r,K > 0
with Changing variables
s Ni+1 = rNi[1Ni/K] implies that when Ni=K,
the birthrate is zero. When Ni>K, the
the
birthrate is negative. Since a negative
birthrate is meaningless, we can only
interpret results when Ni<=K. s To simplify, let xi=Ni/K. Then
To
x xi+1 = r xi (1xi)
(1 Interactive demonstration
s (Demonstration D6) Parameter line
s From our modeling, we conclude that the
From
system shows qualitatively different
qualitatively
behavior for different parameter (r) values.
behavior
s We can construct a parameter line to
We
illustrate this.
illustrate
monotonic 0 monotonic 1 unstable
oscillatory oscillatory 2 3 r Parameter line
s We can construct this line by hand (by
We
exploring the behavior of the system using
the spreadsheet) or we can try to automate
the construction of the line.
the
s To do so we can try to find the “final” or
To
steady state value of the system for various
steady
values of r. Finding steady states
s The spreadsheet we have looked at
The uses a row for each generation of
insects. We don’t necessarily know
how many generations are required to
reach a steady state. One solution is to
expand the sheet to include “many”
cells and assume that if there is a
steady state it will be reached.
steady Finding steady states using
circular references
s Another solution is to use a single cell
Another and a circular reference
and
s In this case, how do we initialize the
In
system (to something other than zero)?
system
s We can use a flag to trigger
flag
initialization and an IF function to
IF
check the flag
check Initializing using a flag and an IF
function
s We choose a cell (e.g., A2) to show
We
A2 to whether we want the system to be
initialized or allowed to iterate to a
possible steady state
possible
s We choose a cell (e.g., B2) to contain
We
B2 to
the initial value desired
the Initializing using a flag and an IF
function
s We choose a cell (e.g., C2) to hold the
We
C2 to value of the system and enter an IF
function along with the system formula
(e.g., xi+1=(xi+3)/2). For example, IF(A2=0,B2,(C2+3)/2) Initializing using a flag and an IF
function
s To allow the system to be evaluated,
To set the flag to zero, then set it to
zero then
something nonzero
nonzero
s Note that this must be done each time
Note
each
reevaluation is desired, i.e. to see the
effect of changing the initial value
effect Interactive demonstration
s Modify Demonstration D6 to use a single
Modify
row of cells to calculate the “final” value of
xi for each value of r (use a flag to initialize)
(use s Modify further to use two rows to calculate
Modify
the “final” values starting from two initial
values (e.g., starting from generation 1 and
from generation 2)
from Interactive demonstration
s Calculate the difference to see if “final”
Calculate
value is stable and show qualitative
conclusion for each value of r Steadystate solutions
s s We conclude for certain values of r that the final
We
value of xi seems to vary with r. What determines
What
the final value?
the
We can solve the recursion relation for a steadystate value. To do so, we look for values of xi for
state
which xi+1 is the same, i.e.,
xi = xi+1 = xi
r(1 xi)
(1 Steadystate solutions
s There are two solutions to the equation
xi = xi r(1 xi)
(1 s (1) ? Steadystate solutions
s There are two solutions to the equation
xi = xi r(1 xi)
(1 s (1) xi = 0
(1) and
and
s (2) ? Steadystate solutions
s There are two solutions to the equation
xi = xi r(1 xi)
(1 s (1) xi = 0
(1) and
and
s (2) xi = 1  1/r
(2) Steadystate solutions
s Which solution is actually observed or if a
if
steady state is ever reached depends (1) on
the value of r and (2) on the initial value of
xi (x1) s For example, if x1 = 0 we can never get to
For
the other steady state 1  1/r
the Steadystate solutions
s We can mathematically determine which
We
solution will apply and whether it can be
reached by analysis of the approach to the
steadystate, which involves examining the
steadystate which
derivative of xi in the neighborhood of the
solutions (e.g., by a Taylor expansion)
solutions Steadystate solutions
s Alternatively, we can numerically
Alternatively,
(computationally) determine which solution
will apply by simulation as we have done.
will Interactive demonstration
s Compare the “final” values in the modified
Compare
Demonstration D6 to 1 1/r to see if the
Demonstration
steady state has been reached.
steady Parameter line s We can update our parameter line to
We
include the steady state values (if any).
include
x=1r1
monotonic x=0
monotonic 0 1 x=1r1
oscillatory 2 unstable
oscillatory 3 r Transition values
s Our modeling also reveals transition
Our
values, values of the parameter that separate
values values
one type of behavior from another
(e.g., r=1). Summary: Parameter Line
s We use a parameter line to visually
We
parameter
summarize the behavior of a system for
different values of the parameter
different
s To create a parameter line we identify all
To
transition values (values where the system
transition
changes its behavior qualitatively) of the
parameter and then write as complete a
description as possible of the behavior of
the system for each region created by the
transition values
transition ...
View
Full Document
 Fall '10
 Staff
 Biology, Computational Biology, Selfreference, MANUAL RECALCULATION, automatic recalculation, circular references

Click to edit the document details