Simplify Cell Formulas - AGAIN

[mtonkovich]

Gang,

Some time back I posted to the group my frustration with complex cell
formulas. Many in the group helped with ideas. I'm writing this note
because I still think there must be a better way. Below is my latest
attempt to simplify using a combination of named ranges and the
INDIRECT function.


BEFORE:


((INDEX(PHFD,$L92)-((INDEX(HrvstFD,$L92))*(INDEX(WNRL,$L92))))*(1-(INDEX(PH­MFD,$L92)))*(1-(INDEX(SNHMYD,$L92))))



AFTER:


(((INDIRECT(AA4) PHFD)-((INDIRECT(AB4) WNRL_)*((INDIRECT(AC4)
HrvstFD_)))) * (1-(INDIRECT(AD4) PHMFD_))) * (1-(INDIRECT(AD4)
SNHMYD_))


The only advantage of the second option is that the formula is a bit
easier to understand (at least for folks that I might be sharing this
with). Unfortunately, it is longer.


I've thought about writing a custom function to do the math behind the
scenes, but quickly realized that would help little if any. Passing
the arguments to the function would take up as much space.


If anyone has any ideas at all as to how I might simplify this formula
(I have others that are 3x this size), I would be grateful if you would

share them with me.


And yes, I thought about parsing the formula into 2 or 3 cells and
combine those results. Ultimately, I may have to go that route.


Regards,


Mike

 

[Bob Phillips]

See response in public.excel

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Gang,

Some time back I posted to the group my frustration with complex cell
formulas. Many in the group helped with ideas. I'm writing this note
because I still think there must be a better way. Below is my latest
attempt to simplify using a combination of named ranges and the
INDIRECT function.


BEFORE:


((INDEX(PHFD,$L92)-((INDEX(HrvstFD,$L92))*(INDEX(WNRL,$L92))))*(1-(INDEX(PH­
MFD,$L92)))*(1-(INDEX(SNHMYD,$L92))))



AFTER:


(((INDIRECT(AA4) PHFD)-((INDIRECT(AB4) WNRL_)*((INDIRECT(AC4)
HrvstFD_)))) * (1-(INDIRECT(AD4) PHMFD_))) * (1-(INDIRECT(AD4)
SNHMYD_))


The only advantage of the second option is that the formula is a bit
easier to understand (at least for folks that I might be sharing this
with). Unfortunately, it is longer.


I've thought about writing a custom function to do the math behind the
scenes, but quickly realized that would help little if any. Passing
the arguments to the function would take up as much space.


If anyone has any ideas at all as to how I might simplify this formula
(I have others that are 3x this size), I would be grateful if you would

share them with me.


And yes, I thought about parsing the formula into 2 or 3 cells and
combine those results. Ultimately, I may have to go that route.


Regards,


Mike