Nested IFs (Excel 2002)

Topic

New Reply

Is there a limit to the number of IFs you can nest?

I have a bunch of characters I need to evaluate and return one of three words. (I don’t seem to be able to use ranges because they’re a mix of letters and numbers.) My format is working, but as soon as the formula wraps to a second line, it stops working. The whole thing looks like this: =IF(B2=X123,”ONE”,IF(B2=X133,”ONE”,IF(B2=X143,”ONE”,IF(B2=X250,”ONE”,IF(B2=X350,”ONE”,IF(B2=X450,”ONE”,IF(B2=X380,”TWO”,IF(B2=X390,”TWO”,IF(B2=X900,”TWO”,”THREE”))))))))) — the values are fictitious, but suffice it to say they’re overlapping ranges.

If I shorten the formula so it stays on one line, it works fine, which tells me I probably have the formatting right . Of course, I can’t do that!

Any ideas of why this is happening, how to get around it, or maybe how to do what I need another way? (I’m kind of an intermediate Excel user, btw.)

Reply | Quote

Replies

7 nested functions: For workarounds see Chip Pearson’s site or John Walkenbach’s site

Steve

Reply | Quote

Thanks so much. That gives me someplace to go, anyway!

Reply | Quote

If you need additional help, please post back.

Steve

Reply | Quote

Actually, I ended up using CONCATENATE, the & form of it. Works like a charm and should be easy enough for others to understand.

Thanks for everyone’s replies!

Reply | Quote

Actually, I ended up using CONCATENATE, the & form of it. Works like a charm and should be easy enough for others to understand.

Thanks for everyone’s replies!

Reply | Quote

If you need additional help, please post back.

Steve

Reply | Quote

(Edited by JohnBF on 08-Mar-04 12:17. Added then corrected formula.)

In appropriate circumstances, =CHOOSE(arg,return if 1,return if 2, return if 3, etc …) can also be useful for this kind of problem. See if

=CHOOSE(1+OR(B2=X123,B2=X133,B2=X143,B2=X250,B2=X350,B2=X450)*2+OR(B2=X380,B2=X390,B2=X900)*3,”THREE”,”ONE”,”TWO”)

works for you.

Reply | Quote

(Edited by JohnBF on 08-Mar-04 12:17. Added then corrected formula.)

In appropriate circumstances, =CHOOSE(arg,return if 1,return if 2, return if 3, etc …) can also be useful for this kind of problem. See if

=CHOOSE(1+OR(B2=X123,B2=X133,B2=X143,B2=X250,B2=X350,B2=X450)*2+OR(B2=X380,B2=X390,B2=X900)*3,”THREE”,”ONE”,”TWO”)

works for you.

Reply | Quote

Thanks so much. That gives me someplace to go, anyway!

Reply | Quote

7 nested functions: For workarounds see Chip Pearson’s site or John Walkenbach’s site

Steve

Reply | Quote

Take a look at VLOOKUP. You can place your values in a table and be able to return the answer based on some criteria.

Reply | Quote

Take a look at VLOOKUP. You can place your values in a table and be able to return the answer based on some criteria.

Reply | Quote

The nesting limit is 7. This could easily be done by building a table on another worksheet and using VLOOKUP to get the value from the table.

Reply | Quote

The nesting limit is 7. This could easily be done by building a table on another worksheet and using VLOOKUP to get the value from the table.

Reply | Quote