| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))))) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))))))))) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))))))))) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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)))))))))) |
|
#5
|
|||
|
|||
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:) Quote:
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
