Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Hard-coded cell values to constants
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Hard-coded cell values to constants
I tried to get ahold of your sample sheet but IE gave me an error report when
i tried to copy that into my web browser. (Perhaps I'm doing something wrong.) I'd be interested in looking over the sheet and trying to help out (I used to work for Alberta Fish and Wildlife and long ago did some elk game ranching inventory with Lotus.) "Takeadoe" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Hard-coded cell values to constants
Tim - I'd be happy to email it to you if you would like to drop me your email
address. Mike "Tim M" wrote: I tried to get ahold of your sample sheet but IE gave me an error report when i tried to copy that into my web browser. (Perhaps I'm doing something wrong.) I'd be interested in looking over the sheet and trying to help out (I used to work for Alberta Fish and Wildlife and long ago did some elk game ranching inventory with Lotus.) "Takeadoe" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update of cell values after insert row? | Excel Worksheet Functions | |||
Counting Multiple Values In A Cell | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
set hypothetical values for a cell | Excel Worksheet Functions |