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.


BEFO


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

Please decide where you want your answer and ask there

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
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.


BEFO


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

Again, I apologize. Not sure where the answer would come from, I asked
in multiple places. I guess I don't know the groups well enough to
know where to place my question.

Mike
Don Guillett wrote:
Please decide where you want your answer and ask there

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
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.


BEFO


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