when good formulas go bad
 

=INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))
my simple lookup formula above turned in to this nightmare....would you guys
persevere with this or go to one basic table based on the above formula with
two or three extra lookup tables to deal with all the extra pricing
variables.

=IF(ISERROR(INDEX(INDIRECT(R$93),MATCH($C8,INDIREC T(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))),"not available",
IF($F8="",INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT( R$94),-1),MATCH($E8,INDIRECT(R$95),-1)),
IF(ISERROR(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE)), "not available",
IF(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)<1,INDEX(IND IRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))*(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE))+IF(ISN A(HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),
INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))+(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)+IF(ISNA(H LOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)))))))

this is a formula to retrive products prices of different sizes (some of
which arent available in all sizes) with surcharges if modifications are
selected.Some modifications arent applicable to all products,some
modifications are straight dollar sucharge,some are a percentage of the
original price while others may have a price or percentage surcharge and a
surchage dependent on width or height.It seemed easy at first(response to a
forum post)
--
paul
remove nospam for email addy!

when good formulas go bad
 

Hi!

would you guys
persevere with this or go to one basic table based on the above formula
with
two or three extra lookup tables to deal with all the extra pricing
variables.

I would use the basic table with sub-tables for the all the "added junk".

OR, perhaps you can build one table with all the "added junk" built-in.

Monster formulas are a real pain to "fix" 6 months after you've created
them. Right now it's probably fresh in your mind but 6 months from
now...........WTF!!!!!!!!!

Biff

"paul" wrote in message
...
=INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))
my simple lookup formula above turned in to this nightmare....would you
guys
persevere with this or go to one basic table based on the above formula
with
two or three extra lookup tables to deal with all the extra pricing
variables.

=IF(ISERROR(INDEX(INDIRECT(R$93),MATCH($C8,INDIREC T(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))),"not
available",
IF($F8="",INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT( R$94),-1),MATCH($E8,INDIRECT(R$95),-1)),
IF(ISERROR(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE)), "not available",
IF(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)<1,INDEX(IND IRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))*(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE))+IF(ISN A(HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),
INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))+(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)+IF(ISNA(H LOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)))))))

this is a formula to retrive products prices of different sizes (some of
which arent available in all sizes) with surcharges if modifications are
selected.Some modifications arent applicable to all products,some
modifications are straight dollar sucharge,some are a percentage of the
original price while others may have a price or percentage surcharge and a
surchage dependent on width or height.It seemed easy at first(response to
a
forum post)
--
paul
remove nospam for email addy!

when good formulas go bad
 

exactly Biff.I have written the formula for another poster,and it works as
far as i know given the information i have received.I want to make it simpler
for him to debug,add ,change as required....oh well i can use most of the
formulas in the tables i guess.......
--
paul
remove nospam for email addy!



"Biff" wrote:

Hi!

would you guys
persevere with this or go to one basic table based on the above formula
with
two or three extra lookup tables to deal with all the extra pricing
variables.

I would use the basic table with sub-tables for the all the "added junk".

OR, perhaps you can build one table with all the "added junk" built-in.

Monster formulas are a real pain to "fix" 6 months after you've created
them. Right now it's probably fresh in your mind but 6 months from
now...........WTF!!!!!!!!!

Biff

"paul" wrote in message
...
=INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))
my simple lookup formula above turned in to this nightmare....would you
guys
persevere with this or go to one basic table based on the above formula
with
two or three extra lookup tables to deal with all the extra pricing
variables.

=IF(ISERROR(INDEX(INDIRECT(R$93),MATCH($C8,INDIREC T(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))),"not
available",
IF($F8="",INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT( R$94),-1),MATCH($E8,INDIRECT(R$95),-1)),
IF(ISERROR(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE)), "not available",
IF(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)<1,INDEX(IND IRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))*(1+VLOOKUP($F8,INDIRECT(R$96),2,FALSE))+IF(ISN A(HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),
INDEX(INDIRECT(R$93),MATCH($C8,INDIRECT(R$94),-1),MATCH($E8,INDIRECT(R$95),-1))+(VLOOKUP($F8,INDIRECT(R$96),2,FALSE)+IF(ISNA(H LOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)),0,HLOOKUP(I$7,$S$7:$W$32,ROW(S8)-6,FALSE)))))))

this is a formula to retrive products prices of different sizes (some of
which arent available in all sizes) with surcharges if modifications are
selected.Some modifications arent applicable to all products,some
modifications are straight dollar sucharge,some are a percentage of the
original price while others may have a price or percentage surcharge and a
surchage dependent on width or height.It seemed easy at first(response to
a
forum post)
--
paul
remove nospam for email addy!