Convert Hard-coded cell values to constants

[Takeadoe]

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. 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.

If I pique your curiosity with this note, you'll probably find the file
contents much easier to follow. I would be happy to send you a sample
to look at.

Thank you.

 

[Miguel Zapico]

One approach to this may be the use of names. You can set up the bunch of
constants in a new worksheet, define a name for each one (through the name
box to the left of the formula bar, or menu Insert->Name->Define), and change
your formulas to use those names instead of cell references.
The process of change references to name should be a one time effort, and
maybe you can use find/replace in your particular case to ease it a bit.

Hope this helps,
Miguel.

 

[RagDyeR]

This should give you an idea or so:

=(A1+B1)*0.25

To make the 0.25 an easily revised variable, use a cell address (say Y1),
where you can change the value in that *single cell*, and revise all the
formulas.

=(A1+B1)*$Y$1

Now, if you would like to change either A1 or B1 in this formula, and have
those locations controlled from a single cell (say Z1), try this:

=(INDIRECT(Z1)+B1)*$Y$1

If this doesn't help, post back with a rephrased question.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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. 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.

If I pique your curiosity with this note, you'll probably find the file
contents much easier to follow. I would be happy to send you a sample
to look at.

Thank you.