Num Error #2

[Shu of AZ]

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

=IF(AND(G38>5.4,G38<6.1,P38<60),((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38)-(N38))),

IF(AND(G38>5.4,G38<6.1,P38>60),((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(G38>8.9,G38<15,P38<200),((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-100+60)-(N38-100+60))),

IF(AND(G38>8.9,G38<15,P38>200),((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-200+120)-(N38-100+60))))))))))

 

[pinmaster]

Try:
=IF(AND(G38>5.4,G38<6.1,P38<60),((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10.5))/((P38)-(N38))),IF(AND(G38>5.4,G38<6.1,P38>60),((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(G38>8.9,G38<15,P38<200),((VLOOKUP(G38,CU142:CZ158,6,FALSE)-(L38*10)+(J38*10))/((P38-100+60)-(N38-100+60))),IF(AND(G38>8.9,G38<15,P38>200),((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

 

[David Biddulph]

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.