About Us

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Change case...help please	Terry	Excel Worksheet Functions	14	October 2nd 05 12:29 PM
Date & Time	mully	New Users to Excel	4	May 23rd 05 11:56 AM
Automatically up date time in a cell	Mark	Excel Discussion (Misc queries)	5	May 12th 05 12:26 AM
clock	Wildman	Excel Worksheet Functions	2	April 26th 05 10:31 AM
Make Change Case in Excel a format rather than formula	Kevin	Excel Worksheet Functions	1	March 18th 05 08:53 PM

Menu

About Us