Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested IFs (Excel 2002)

    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,"ON E",IF(B2=X380,"TWO",IF(B2=X390,"TWO",IF(B2=X900,"T WO","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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Of course, I can't do that! <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nested IFs (Excel 2002)

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

    Steve

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IFs (Excel 2002)

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

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IFs (Excel 2002)

    Take a look at VLOOKUP. You can place your values in a table and be able to return the answer based on some criteria.
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nested IFs (Excel 2002)

    If you need additional help, please post back.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Nested IFs (Excel 2002)

    <P ID="edit" class=small>(Edited by JohnBF on 08-Mar-04 12:17. Added then corrected formula.)</P>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=X3 50,B2=X450)*2+OR(B2=X380,B2=X390,B2=X900)*3,"THREE ","ONE","TWO")

    works for you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IFs (Excel 2002)

    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.
    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IFs (Excel 2002)

    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!
    Thanks - - - Sher

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •