|Subject:||Convert Hard-coded cell values to constants|
|Date:||Fri, 19 May 2006|
If you have not guessed, I manage deer - lots of them in lots of
counties. For that reason, I'm trying to automate my population
models. For anyone with an interest, I've placed a file called
ASM Parameters Only Example.xls on our FTP site
.. If I pique your curiosity with
this note, you'll probably find the file contents much easier to
follow. In short, what I've developed is a simple accounting-style
population model that begins with a known population in 1981 and
attempts to mimic/model/simulate/track the population through time.
The population is composed of 6 age/sex classes. We'll use fawn does
as an example.
The name "accounting" comes from the fact that the model is like a
simple checking account, with simple deposits and withdrawals. As
you might imagine, withdrawals come in many forms - harvest,
wounding and nonreported harvest, winter mortality, and summer
mortality. The only deposit is spring births.
Thus the Fall 1982 fawn population estimate would be derived as
follows: mind you, all of this is being done inside a single cell.
PHFD1982=(((PHFD1981 - (HRVSTFD81*wnrl81))* (1-phmfd))
Presently, all of the values in the above equation are actually cell
references - that is they literally point to a cell to grab the
value for that year. This needs to be changed for 2 reasons. With
the exception of PHFD1981 and HRVSTFD81, the others are constant
over the 25-year modeling period, or perhaps change 1 time. The
other reason is ease of fine-tuning. If I want to adjust the
wounding and nonreporting rate, I have to do that in every cell!
That has to be the epitome of inefficiency! What I was hoping to do
was create a bunch of constants that would replace the cell
references in the above formula. This would allow me to easily
change the values across all years from a single place. And this is
where I need some help. I'm not real sure about how to do this.
Any assistance on this would really be appreciated. I'm just looking
for some rough ideas. Again, take a look at the file and you'll get
a much better sense for what I'm doing and what I would like to do.