Home |
Search |
Today's Posts |
#1
|
|||
|
|||
my code too long?
i am writing a multiple criteria code for a table. its a bit over
complicated, but it should work fine. the following: =(IF(D4="MIL-C-13931","X",IF(D4="ANSI Y14.5M","X",IF(D4="ANSI Y14.5M-1982","X",IF(D4="B8769067","X",IF(D4="A7309999","X ",IF(D4="mil-l-3150","X",IF(D4="mil-g-10924","X",IF(D4="mil-c-46168","X"," "))))))))) code works. It replaces the matching textx with an X. the following: =(IF(D4="MIL-C-13931","X",IF(D4="ANSI Y14.5M","X",IF(D4="ANSI Y14.5M-1982","X",IF(D4="B8769067","X",IF(D4="A7309999","X ",IF(D4="mil-l-3150","X",IF(D4="mil-g-10924","X",IF(D4="mil-c-46168","X",IF(D4="mil-c-13924","X"," "))))))))) does not work. The number of parenthesis at the end is not the problem, because if i am missing one or have one too many excel just does it for me. instead, i am getting an error message like my code is bad--yet i feel like i haven't changed much about the code except added another IF. can anyone see it? |
#2
|
|||
|
|||
Zach wrote...
i am writing a multiple criteria code for a table. its a bit over complicated, but it should work fine. the following: =(IF(D4="MIL-C-13931","X", IF(D4="ANSI Y14.5M","X", IF(D4="ANSI Y14.5M-1982","X", IF(D4="B8769067","X", IF(D4="A7309999","X", IF(D4="mil-l-3150","X", IF(D4="mil-g-10924","X", IF(D4="mil-c-46168","X", " "))))))))) code works. It replaces the matching textx with an X. . . . This works because Excel's formula parser allows 7 levels of *nested* function calls. You have 8 IF calls, with the final one at the 7th *nested* level (the first/outermost IF call isn't nested). . . . the following: =(IF(D4="MIL-C-13931","X", IF(D4="ANSI Y14.5M","X", IF(D4="ANSI Y14.5M-1982","X", IF(D4="B8769067","X", IF(D4="A7309999","X", IF(D4="mil-l-3150","X", IF(D4="mil-g-10924","X", IF(D4="mil-c-46168","X", IF(D4="mil-c-13924","X", " "))))))))) does not work. .... This fails because the final/9th IF call would be at the 8th nested function call level, and Excel's formula parser doesn't support that. That said, your approach is inefficient. This could be done with an outer If call and a single OR call since you're only checking the value of cell D4 against several alternatives. Try =IF(OR(D4={"MIL-C-13931","ANSI Y14.5M","ANSI Y14.5M-1982", "B8769067","A7309999","mil-l-3150","mil-g-10924", "mil-c-46168","mil-c-13924"}),"X","") Note: this also removes the unnecessary outmost parentheses and changes the FALSE term from " " to "". You'll find that "" will save you much grief compared to " " over the long haul. Both will appear the same. |
#3
|
|||
|
|||
That worked Great, Thanks so much.
"Harlan Grove" wrote: Zach wrote... i am writing a multiple criteria code for a table. its a bit over complicated, but it should work fine. the following: =(IF(D4="MIL-C-13931","X", IF(D4="ANSI Y14.5M","X", IF(D4="ANSI Y14.5M-1982","X", IF(D4="B8769067","X", IF(D4="A7309999","X", IF(D4="mil-l-3150","X", IF(D4="mil-g-10924","X", IF(D4="mil-c-46168","X", " "))))))))) code works. It replaces the matching textx with an X. . . . This works because Excel's formula parser allows 7 levels of *nested* function calls. You have 8 IF calls, with the final one at the 7th *nested* level (the first/outermost IF call isn't nested). . . . the following: =(IF(D4="MIL-C-13931","X", IF(D4="ANSI Y14.5M","X", IF(D4="ANSI Y14.5M-1982","X", IF(D4="B8769067","X", IF(D4="A7309999","X", IF(D4="mil-l-3150","X", IF(D4="mil-g-10924","X", IF(D4="mil-c-46168","X", IF(D4="mil-c-13924","X", " "))))))))) does not work. .... This fails because the final/9th IF call would be at the 8th nested function call level, and Excel's formula parser doesn't support that. That said, your approach is inefficient. This could be done with an outer If call and a single OR call since you're only checking the value of cell D4 against several alternatives. Try =IF(OR(D4={"MIL-C-13931","ANSI Y14.5M","ANSI Y14.5M-1982", "B8769067","A7309999","mil-l-3150","mil-g-10924", "mil-c-46168","mil-c-13924"}),"X","") Note: this also removes the unnecessary outmost parentheses and changes the FALSE term from " " to "". You'll find that "" will save you much grief compared to " " over the long haul. Both will appear the same. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |