software

Folks - My question relates to simplifying a formula (they're too
long for the cell!). I thought it might help to provide some
background. Sorry for all the detail, but I thought it might help the
cause!

Goal: Calculate the size of the Fall 1982, doe fawn population in
Adams County. Realize that 3 age classes of does (fawns, yearlings,
and adults) give birth each spring and thus contribute to the fall fawn
class.While the formulas below look complicated, they really are quite
simple. Each formula does the same thing. The only difference is the
doe (female deer) age class being considered. Since all 3 age classes
(fawns, yearlings, and adults) have fawns, but have different birth and
mortality rates, they have to be treated separately.The process begins with the estimated size of respective age class Fall
1981 (FD1981=354). From that, we subtract the reported harvest
(FDH81=38). Since some deer are shot and not recovered and some
hunters don't report their deer, we have to adjust the reported
harvest for wounding and nonreporting (WNR81=31%). Some deer will die
in the winter of nonharvest (natural deaths) related causes. We have
to subtract that from the number left after the hunting season
(WNHM=9%). We are now in Spring and the Fawns are now 1 year old and
about to give birth. Thus, we apply the reproductive rate (FRR=0.85
fawns produced per doe in the population) to the size of the spring
fawn population to get the number of fawns born. Since the sex ratio
at birth is roughly spilt between boys and girls, we multiply by the
percent females, which is about 46%. Now we have the number of female
fawns born. Some will die in the summer and we have to adjust for
that. For this population, we estimate summer mortality to be roughly
29%. If you do the math, you'll find that the 354 doe fawns alive at
the start of the Fall 1981 hunting season recruited (born and survived
to the Fall 1982 population) approximately 125 doe fawns. Pretty
simply, huh!The problem is, this process has to be repeated 2 more times for the
other 2 age classes of does and the results for each of the 3 groups
added together to get the final number. Not only can I not get all of
this into a single cell (Excel gave me an error message saying the
formula was too long), it would be next to impossible for someone to
follow, including myself.So, what are my choices? Obviously, I could calculate the 3 values
separately, place them onto the worksheet and then have a simple
formula in the Fall 1982 fawn doe cell to sum the 3 values. Not really
what I want to do, since no one needs extra data lying around and it
seems inefficient. What I was hoping that I could do was create some
alias for each of the 3 really long formulas and place them in the
cells. The only thing that I could come up with there is using the
Define Name process, but that would be a nightmare, as I have 88
counties and 25 years of data. Can anyone think of anything else that
I might try?Fawn Recruitment from Fawn Does:
=(((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(G1),0))-((VLOOKUP($B92,'ASM
Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
Data'!$A$3:$AX$4500,COLUMN(H1),0)))))*(1-(VLOOKUP($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(L1),0))))*(VLOOKU P($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(V1),0)))*(VLOOKUP ($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(S1),0))Fawn Recruitment from Yearling Does:
(((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(H1),0))-((VLOOKUP($B92,'ASM
Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
Data'!$A$3:$AX$4500,COLUMN(I1),0)))))*(1-(VLOOKUP($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(M1),0))))*(VLOOKU P($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(W1),0)))*(VLOOKUP ($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(T1),0))Fawn Recruitment from Adult Does:
(((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(I1),0))-((VLOOKUP($B92,'ASM
Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
Data'!$A$3:$AX$4500,COLUMN(J1),0)))))*(1-(VLOOKUP($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(N1),0))))*(VLOOKU P($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(X1),0)))*(VLOOKUP ($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(U1),0))ReplyIf I can suggest- altho you would generally prefer to have the entire
calculation performed in one cell (quot;since no one needs extra data lying
around and it seems inefficientquot;), what happens if you depart the job?
You said yourself the logic is hard to follow- if this was my task I
would devote a column with a multiplier constant for each element that
affects population. It might look like beginning population reported
harvest nonreported harvest adjustment - deaths from natural causes
births etc etc etc.

The value to this is ease of understanding the logic flow (and thereby,
ease of transition for the person who takes this job when you become
boss of the dept), and ease of changing data constants (if 31% becomes
29%, for instance). You would be able to easily add a column to adjust
the population due to an actual population count (assuming the deer
return the census forms) and identify it as such; since the ending
population one year is the beginning population the next year, a simple
cell reference will do the work for you; you can adjust the number of
times your formulas perform a rounding operation. If you don't want to
look at all those calculations you can hide the columns, and show just
the beginning and ending populations, for instance. I know I'm totally
editorializing, here, but my opinion is: simplicity is your friend.
Try explaining the formulas you posted to your boss- unless he's a
total Excel geek he'll be dazed and confused, and most likely ask you
to make it easier to understand. But that's just me, Mr. Vegas.If you don't want to mess with the formula, you may try this:
1.- Use names for the ranges $A$4:$AZ$4579 and 'ASM
Parameters'!$A$3:$AX$4500, they appear in all the formulas and you can save
space and gain in clarity if you use a short name for them.
2.- I don't if you need them to be dynamic, you may harcode the values of
all the COLUMN formulas that you have (13 instead of COLUMN(M1) and so on)

If you are in the mood of trying new things, check the help for the INDEX
formula, you may find a way of making it work for you.

Hope this helps,
Miguel.

quot;Takeadoequot; wrote:

gt; Folks - My question relates to simplifying a formula (they're too
gt; long for the cell!). I thought it might help to provide some
gt; background. Sorry for all the detail, but I thought it might help the
gt; cause!
gt;
gt; Goal: Calculate the size of the Fall 1982, doe fawn population in
gt; Adams County. Realize that 3 age classes of does (fawns, yearlings,
gt; and adults) give birth each spring and thus contribute to the fall fawn
gt; class.
gt;
gt;
gt; While the formulas below look complicated, they really are quite
gt; simple. Each formula does the same thing. The only difference is the
gt; doe (female deer) age class being considered. Since all 3 age classes
gt; (fawns, yearlings, and adults) have fawns, but have different birth and
gt; mortality rates, they have to be treated separately.
gt;
gt;
gt; The process begins with the estimated size of respective age class Fall
gt; 1981 (FD1981=354). From that, we subtract the reported harvest
gt; (FDH81=38). Since some deer are shot and not recovered and some
gt; hunters don't report their deer, we have to adjust the reported
gt; harvest for wounding and nonreporting (WNR81=31%). Some deer will die
gt; in the winter of nonharvest (natural deaths) related causes. We have
gt; to subtract that from the number left after the hunting season
gt; (WNHM=9%). We are now in Spring and the Fawns are now 1 year old and
gt; about to give birth. Thus, we apply the reproductive rate (FRR=0.85
gt; fawns produced per doe in the population) to the size of the spring
gt; fawn population to get the number of fawns born. Since the sex ratio
gt; at birth is roughly spilt between boys and girls, we multiply by the
gt; percent females, which is about 46%. Now we have the number of female
gt; fawns born. Some will die in the summer and we have to adjust for
gt; that. For this population, we estimate summer mortality to be roughly
gt; 29%. If you do the math, you'll find that the 354 doe fawns alive at
gt; the start of the Fall 1981 hunting season recruited (born and survived
gt; to the Fall 1982 population) approximately 125 doe fawns. Pretty
gt; simply, huh!
gt;
gt;
gt; The problem is, this process has to be repeated 2 more times for the
gt; other 2 age classes of does and the results for each of the 3 groups
gt; added together to get the final number. Not only can I not get all of
gt; this into a single cell (Excel gave me an error message saying the
gt; formula was too long), it would be next to impossible for someone to
gt; follow, including myself.
gt;
gt;
gt; So, what are my choices? Obviously, I could calculate the 3 values
gt; separately, place them onto the worksheet and then have a simple
gt; formula in the Fall 1982 fawn doe cell to sum the 3 values. Not really
gt; what I want to do, since no one needs extra data lying around and it
gt; seems inefficient. What I was hoping that I could do was create some
gt; alias for each of the 3 really long formulas and place them in the
gt; cells. The only thing that I could come up with there is using the
gt; Define Name process, but that would be a nightmare, as I have 88
gt; counties and 25 years of data. Can anyone think of anything else that
gt; I might try?
gt;
gt;
gt; Fawn Recruitment from Fawn Does:
gt; =(((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(G1),0))-((VLOOKUP($B92,'ASM
gt; Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
gt; Data'!$A$3:$AX$4500,COLUMN(H1),0)))))*(1-(VLOOKUP($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(L1),0))))*(VLOOKU P($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(V1),0)))*(VLOOKUP ($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(S1),0))
gt;
gt;
gt; Fawn Recruitment from Yearling Does:
gt; (((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(H1),0))-((VLOOKUP($B92,'ASM
gt; Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
gt; Data'!$A$3:$AX$4500,COLUMN(I1),0)))))*(1-(VLOOKUP($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(M1),0))))*(VLOOKU P($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(W1),0)))*(VLOOKUP ($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(T1),0))
gt;
gt;
gt; Fawn Recruitment from Adult Does:
gt; (((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(I1),0))-((VLOOKUP($B92,'ASM
gt; Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
gt; Data'!$A$3:$AX$4500,COLUMN(J1),0)))))*(1-(VLOOKUP($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(N1),0))))*(VLOOKU P($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(X1),0)))*(VLOOKUP ($B92,'ASM
gt; Parameters'!$A$3:$AX$4500,COLUMN(U1),0))
gt;
gt;
gt; Reply
gt;

Miquel,

Thank you for taking time to reply. You had some good suggestions that I
will take a closer look at. I'll have to ponder the Index function a bit
more. I couldn't see an immediate use for it, but I will need to study it
longer.

Have a great evening and thank you again for your time.

Mucho gracias!

Mike

quot;Miguel Zapicoquot; wrote:

gt; If you don't want to mess with the formula, you may try this:
gt; 1.- Use names for the ranges $A$4:$AZ$4579 and 'ASM
gt; Parameters'!$A$3:$AX$4500, they appear in all the formulas and you can save
gt; space and gain in clarity if you use a short name for them.
gt; 2.- I don't if you need them to be dynamic, you may harcode the values of
gt; all the COLUMN formulas that you have (13 instead of COLUMN(M1) and so on)
gt;
gt; If you are in the mood of trying new things, check the help for the INDEX
gt; formula, you may find a way of making it work for you.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;Takeadoequot; wrote:
gt;
gt; gt; Folks - My question relates to simplifying a formula (they're too
gt; gt; long for the cell!). I thought it might help to provide some
gt; gt; background. Sorry for all the detail, but I thought it might help the
gt; gt; cause!
gt; gt;
gt; gt; Goal: Calculate the size of the Fall 1982, doe fawn population in
gt; gt; Adams County. Realize that 3 age classes of does (fawns, yearlings,
gt; gt; and adults) give birth each spring and thus contribute to the fall fawn
gt; gt; class.
gt; gt;
gt; gt;
gt; gt; While the formulas below look complicated, they really are quite
gt; gt; simple. Each formula does the same thing. The only difference is the
gt; gt; doe (female deer) age class being considered. Since all 3 age classes
gt; gt; (fawns, yearlings, and adults) have fawns, but have different birth and
gt; gt; mortality rates, they have to be treated separately.
gt; gt;
gt; gt;
gt; gt; The process begins with the estimated size of respective age class Fall
gt; gt; 1981 (FD1981=354). From that, we subtract the reported harvest
gt; gt; (FDH81=38). Since some deer are shot and not recovered and some
gt; gt; hunters don't report their deer, we have to adjust the reported
gt; gt; harvest for wounding and nonreporting (WNR81=31%). Some deer will die
gt; gt; in the winter of nonharvest (natural deaths) related causes. We have
gt; gt; to subtract that from the number left after the hunting season
gt; gt; (WNHM=9%). We are now in Spring and the Fawns are now 1 year old and
gt; gt; about to give birth. Thus, we apply the reproductive rate (FRR=0.85
gt; gt; fawns produced per doe in the population) to the size of the spring
gt; gt; fawn population to get the number of fawns born. Since the sex ratio
gt; gt; at birth is roughly spilt between boys and girls, we multiply by the
gt; gt; percent females, which is about 46%. Now we have the number of female
gt; gt; fawns born. Some will die in the summer and we have to adjust for
gt; gt; that. For this population, we estimate summer mortality to be roughly
gt; gt; 29%. If you do the math, you'll find that the 354 doe fawns alive at
gt; gt; the start of the Fall 1981 hunting season recruited (born and survived
gt; gt; to the Fall 1982 population) approximately 125 doe fawns. Pretty
gt; gt; simply, huh!
gt; gt;
gt; gt;
gt; gt; The problem is, this process has to be repeated 2 more times for the
gt; gt; other 2 age classes of does and the results for each of the 3 groups
gt; gt; added together to get the final number. Not only can I not get all of
gt; gt; this into a single cell (Excel gave me an error message saying the
gt; gt; formula was too long), it would be next to impossible for someone to
gt; gt; follow, including myself.
gt; gt;
gt; gt;
gt; gt; So, what are my choices? Obviously, I could calculate the 3 values
gt; gt; separately, place them onto the worksheet and then have a simple
gt; gt; formula in the Fall 1982 fawn doe cell to sum the 3 values. Not really
gt; gt; what I want to do, since no one needs extra data lying around and it
gt; gt; seems inefficient. What I was hoping that I could do was create some
gt; gt; alias for each of the 3 really long formulas and place them in the
gt; gt; cells. The only thing that I could come up with there is using the
gt; gt; Define Name process, but that would be a nightmare, as I have 88
gt; gt; counties and 25 years of data. Can anyone think of anything else that
gt; gt; I might try?
gt; gt;
gt; gt;
gt; gt; Fawn Recruitment from Fawn Does:
gt; gt; =(((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(G1),0))-((VLOOKUP($B92,'ASM
gt; gt; Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
gt; gt; Data'!$A$3:$AX$4500,COLUMN(H1),0)))))*(1-(VLOOKUP($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(L1),0))))*(VLOOKU P($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(V1),0)))*(VLOOKUP ($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(S1),0))
gt; gt;
gt; gt;
gt; gt; Fawn Recruitment from Yearling Does:
gt; gt; (((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(H1),0))-((VLOOKUP($B92,'ASM
gt; gt; Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
gt; gt; Data'!$A$3:$AX$4500,COLUMN(I1),0)))))*(1-(VLOOKUP($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(M1),0))))*(VLOOKU P($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(W1),0)))*(VLOOKUP ($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(T1),0))
gt; gt;
gt; gt;
gt; gt; Fawn Recruitment from Adult Does:
gt; gt; (((((VLOOKUP($B92,$A$4:$AZ$4579,COLUMN(I1),0))-((VLOOKUP($B92,'ASM
gt; gt; Parameters'!$A3:$AX4500,COLUMN($J1),0))*(VLOOKUP($ B92,'Harvest
gt; gt; Data'!$A$3:$AX$4500,COLUMN(J1),0)))))*(1-(VLOOKUP($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(N1),0))))*(VLOOKU P($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(X1),0)))*(VLOOKUP ($B92,'ASM
gt; gt; Parameters'!$A$3:$AX$4500,COLUMN(U1),0))
gt; gt;
gt; gt;
gt; gt; Reply
gt; gt;

Dave O - I really enjoyed reading your response. Your time is valuable and I
appreciate you donating some to my cause! Thank you very much. Oddly
enough, I'm retooling all of my models, trying to get rid of some of the
clutter. Imagine this for a second - 88 separate worksheets in 5 workbooks
with all of the intermediate steps in each worksheet! What a mess. If I
keep it confusing for the boss - well I hope they'll have to keep me around.
Once again, I do sincerely appreciate you taking time out to drop me a note.

Regards,

Mike

quot;Dave Oquot; wrote:

gt; If I can suggest- altho you would generally prefer to have the entire
gt; calculation performed in one cell (quot;since no one needs extra data lying
gt; around and it seems inefficientquot;), what happens if you depart the job?
gt; You said yourself the logic is hard to follow- if this was my task I
gt; would devote a column with a multiplier constant for each element that
gt; affects population. It might look like beginning population reported
gt; harvest nonreported harvest adjustment - deaths from natural causes
gt; births etc etc etc.
gt;
gt; The value to this is ease of understanding the logic flow (and thereby,
gt; ease of transition for the person who takes this job when you become
gt; boss of the dept), and ease of changing data constants (if 31% becomes
gt; 29%, for instance). You would be able to easily add a column to adjust
gt; the population due to an actual population count (assuming the deer
gt; return the census forms) and identify it as such; since the ending
gt; population one year is the beginning population the next year, a simple
gt; cell reference will do the work for you; you can adjust the number of
gt; times your formulas perform a rounding operation. If you don't want to
gt; look at all those calculations you can hide the columns, and show just
gt; the beginning and ending populations, for instance. I know I'm totally
gt; editorializing, here, but my opinion is: simplicity is your friend.
gt; Try explaining the formulas you posted to your boss- unless he's a
gt; total Excel geek he'll be dazed and confused, and most likely ask you
gt; to make it easier to understand. But that's just me, Mr. Vegas.
gt;
gt;