my code too long?

[Guest]

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?

 

[Harlan Grove]

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.

 

[Guest]

That worked Great, Thanks so much.

Zach wrote...

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).

....

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.