Jean-Guy question
 

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

Jean-Guy question
 

Hi

Well I figured one of your IF functions must be missing an FALSE arguments
so rather than trying to follow your formula which would probably have given
me a headache I opted for the trial and error. :) Did it fix your problem??

Regards
Jean-Guy

"Shu of AZ" wrote:

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

Jean-Guy question
 

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