Convert Hard-coded cell values to constants

Giganews Newsgroups
Subject: Convert Hard-coded cell values to constants
Posted by:  Takeadoe
Date: Fri, 19 May 2006

Gang -

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
(ftp://ftp.dnr.state.oh.us/Wildlife/pub/upload/New%20Folder%20(5)/)
..  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))
*reprofd*sexrafd)*summort...

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.

Thank you.

Replies