Num Error #2
 

This formula works but I cannot get it to return a zero when the formula is
FALSE

=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(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:C Z158,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,F ALSE)-(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))))))))))

Num Error #2
 

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

"Shu of AZ" wrote:

This formula works but I cannot get it to return a zero when the formula is
FALSE

=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(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:C Z158,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,F ALSE)-(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))))))))))

Num Error #2
 

How do you determine the amount of paranthesis before the 0 and after it.

"pinmaster" wrote:

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

"Shu of AZ" wrote:

This formula works but I cannot get it to return a zero when the formula is
FALSE

=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(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:C Z158,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,F ALSE)-(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))))))))))

Num Error #2
 

Your 2nd IF is in the false value from your 1st condition, similarly your
3rd IF is in the false value from the 2nd IF, and so on. The place you want
your zero is in the false value from your 7th, and final, IF, so comes
before your set of 7 parentheses at the end.

You could make it slightly less confusing as I don't think you need the
outer set of parentheses on each true value, so you could get away with just
one opening parenthesis before each VLOOKUP and just 2 closing before the
comma at the end of the true value.
--
David Biddulph

"Shu of AZ" wrote in message
...
How do you determine the amount of paranthesis before the 0 and after it.

"pinmaster" wrote:

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

"Shu of AZ" wrote:

This formula works but I cannot get it to return a zero when the
formula is
FALSE

=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(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:C Z158,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,F ALSE)-(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))))))))))

hi Shu of AZ

since your formula already works, to replace the result from FALSE in to ZERO,
try the double coercion style, i just learned from this forum, infront of your formula...
= --IF(.......

happy holidays...
4pinoy:)