Ideas for simplifying cell formulas

[Takeadoe]

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))))*(VLOOKUP($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))))*(VLOOKUP($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))))*(VLOOKUP($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(X1),0)))*(VLOOKUP($B92,'ASM
Parameters'!$A$3:$AX$4500,COLUMN(U1),0))


Reply

 

[Dave O]

If I can suggest- altho you would generally prefer to have the entire
calculation performed in one cell ("since no one needs extra data lying
around and it seems inefficient"), 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.

 

[Miguel Zapico]

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.

 

[Takeadoe]

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

 

[Takeadoe]

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