T
Takeadoe
Hey gang -
I've developed a deer population model (1 for each of 88 counties) to
track herd size from 1981 to present. I start with an initial
population estimate (in 3 male and 3 female age groups) in 1981 and
each year add births and subtract known mortalities (aka harvest). I
also make some estimates of non-harvest losses and nonreporting (people
who harvest but do not report). Any how, if I've got all the
parameters (birth rates, survival estimates...) right and I've done a
good job of accounting for all mortality, I should be able to generate
a fall population estimate each year indefinitely. However, if I start
with too many deer in 1981 or conversely too few, the herd either goes
to infinity or extinction in short order. When that happens, I have to
go back to my initial estimates for each of the 6 age groups and tweak
them ever so slightly, update the pivot table, rechart the data and
take a peek. This is a rather slow process, since I have to do this
for 88 counties! The "master" worksheet contains 2280 rows of data -
25 years for each of 88 counties. As you would expect, each year's
(row) population estimate is dependent upon the rows above it within a
given county.
My question for the group is this, would there be an easy way to
"remotely" change the values in each of 6 cells? Here's kind of what I
had in mind:
Would it be reasonable to create a separate worksheet (or block of
cells within the same sheet) and populate this new "subworksheet" with
the 1981 (initial population estimates) estimates for all 88 counties
and then link these cells back to cells in the master table containing
the 1981 estimates. That way, I could make changes quickly to the
initial estimates in the "subworksheet" and they would be reflected in
the "master" spreadsheet. I might mention that in most cases, I will
be adjusting the 1981 estimates in the subworksheet for all 6 age/sex
classes by the same amount - generally a fraction of a percent is all
it takes to make a significant change over 25 years. For example, I
might want to multiply all 6 1981 values for Adams county by 1.075.
The more I think about it, I guess the "sub" worksheet would have to
have 13 columns. Six would hold the original values from the master, 1
cell would hold the "multiplier" (that I adjust) and the other 6 would
contain the results (=A1*Multiplier Cell).
Is this way too primitive of an approach? Am I making it more
complicated that it has to be? Is there a better appraoch? I would
greatly appreciate any ideas you might have. If I've left out critical
details, holler. I also should mention that I was hoping to have an
intern do this, which means I would need to comeup with something that
couldn't be "broke" real easy.
Thank you in advance.
Mike
I've developed a deer population model (1 for each of 88 counties) to
track herd size from 1981 to present. I start with an initial
population estimate (in 3 male and 3 female age groups) in 1981 and
each year add births and subtract known mortalities (aka harvest). I
also make some estimates of non-harvest losses and nonreporting (people
who harvest but do not report). Any how, if I've got all the
parameters (birth rates, survival estimates...) right and I've done a
good job of accounting for all mortality, I should be able to generate
a fall population estimate each year indefinitely. However, if I start
with too many deer in 1981 or conversely too few, the herd either goes
to infinity or extinction in short order. When that happens, I have to
go back to my initial estimates for each of the 6 age groups and tweak
them ever so slightly, update the pivot table, rechart the data and
take a peek. This is a rather slow process, since I have to do this
for 88 counties! The "master" worksheet contains 2280 rows of data -
25 years for each of 88 counties. As you would expect, each year's
(row) population estimate is dependent upon the rows above it within a
given county.
My question for the group is this, would there be an easy way to
"remotely" change the values in each of 6 cells? Here's kind of what I
had in mind:
Would it be reasonable to create a separate worksheet (or block of
cells within the same sheet) and populate this new "subworksheet" with
the 1981 (initial population estimates) estimates for all 88 counties
and then link these cells back to cells in the master table containing
the 1981 estimates. That way, I could make changes quickly to the
initial estimates in the "subworksheet" and they would be reflected in
the "master" spreadsheet. I might mention that in most cases, I will
be adjusting the 1981 estimates in the subworksheet for all 6 age/sex
classes by the same amount - generally a fraction of a percent is all
it takes to make a significant change over 25 years. For example, I
might want to multiply all 6 1981 values for Adams county by 1.075.
The more I think about it, I guess the "sub" worksheet would have to
have 13 columns. Six would hold the original values from the master, 1
cell would hold the "multiplier" (that I adjust) and the other 6 would
contain the results (=A1*Multiplier Cell).
Is this way too primitive of an approach? Am I making it more
complicated that it has to be? Is there a better appraoch? I would
greatly appreciate any ideas you might have. If I've left out critical
details, holler. I also should mention that I was hoping to have an
intern do this, which means I would need to comeup with something that
couldn't be "broke" real easy.
Thank you in advance.
Mike