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

 

[Puppet_Sock]

Folks - My question relates to simplifying a formula (they're too
long for the cell!).

[details snipped]

One fairly obvious suggestion is intermediate columns that do
some sub-portion of the calculation. For example, I see a lot of
lookups in your details that I snipped. Make some intermediate
columns that do these lookups, or some of them. Then the
final column can just reference the lookup column instead of
having the entire formula for the lookup and its sources.

By the way, this is sometimes useful debugging. If your formulas
are not producing what you thought they should, you can look
at the intermediate columns and see if they are right. If they are,
then your bug must be later.

If the extra columns are annoying because they spoil the
presentation, then you start thinking about a presenation sheet.
You do all your calcs on one sheet, get it all right, but don't
worry about presentation. Then you have another sheet that
does nothing but link to the calc sheet, showing the results
in nice tables, pretty formatting, etc.
Socks

 

[Takeadoe]

Socks - The intermediate thing is what I did. Each of the 3 components
were derived in separate cells and then I tried to paste them all into
a single cell. That's when I got the hammer! I was hoping for a
slicker solution, something that would avoid the intermediate steps.
Unfortunately, I really don't see any way around them.

Thank you for your help.

Mike