I can't write for Jean-Guy, but you can always "pretty format" your
formulas. For more on how see
Names and formatted formulas
http://www.tushar-mehta.com/publish_...nalysis/08.htm
I did the needful for your formula to get the below, though you will have to
watch out for line-breaks inserted by my newsreader.
Once the formula was properly structured, a whole bunch of things stood out.
You repeatedly use VLOOKUP(...) with the exact same arguments. You might be
better off putting that function in a separate cell. That way, any future
maintenance, such as changing the lookup range, will require an update of
only one formula. You may also want to do something similar to the other
fragments such as P38-100+60 and N38-100+60.
There is one instance where the formula refers to O38 while in all the other
possible branches the matching cell is N38. Is that intentional?
Neither of the first 2 IFs caters to the condition P38=60. Is that
intentional?
=IF(AND(G385.4,G38<6.1,P38<60),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38)-(N38))),
IF(AND(G385.4,G38<6.1,P3860),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+60)-
(N38))),
IF(OR(G38=6.5,G38=7,G38=7.5),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+60)-
(N38))),
IF(OR(G38=8,G38=8.32,G38=8.5),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+
60)-(O38-100+60))),
IF(OR(G38=9,G38=9.5),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+
60)-(N38-100+60))),
IF(AND(G388.9,G38<15,P38<200),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-100+
60)-(N38-100+60))),
IF(AND(G388.9,G38<15,P38200),
((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-
200+120)-(N38-100+60))),
0)
)
)
)
)
)
)
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article ,
says...
How did you determine the quantity of parathensis before and after the 0?
Try:
=IF(AND(G385.4,G38<6.1,P38<60),((VLOOKUP(G38,CU14 2:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38)-(N38))),IF(AND(G385.4,G38<6.1,P3860),((VLOOKUP(G 38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+60)-(N38))),IF(OR(G38=6.5,G38=7,G38=7.5),((VLOOKUP(G38 ,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+60)-(N38))),IF(OR(G38=8,G38=8.32,G38=8.5),((VLOOKUP(G3 8,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100
+60)-(O38-100+60))),IF(OR(G38=9,G38=9.5),((VLOOKUP(G38,CU142 :CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38-100+60)-(N38-100+60))),IF(AND(G388.9,G38<15,P38<200),((VLOOKUP (G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-100+60)-(N38-100+60))),IF(AND(G388.9,G38<15,P38200),((VLOOKUP (G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-200+120)-(N38-100+60))),0)))))))
notice the last 0 at the end
HTH
Jean-Guy