Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possibly a loaded question, but I think | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Newbie With A Question | Excel Worksheet Functions | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |