Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Oxford, England
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IFs can you nest or extend them? (Office 2000)

    Hi, very new to Excel and am trying on day one of new job to nest or extend an if statement. What I'm trying to do is "if cell c7 is equal to "town1" then use number in cell B2 else if cell c7 is equal to "town2" then use number in cell B3 else if cell C7 is equal to "town3" then use number in cell B4" I cant seem to successfully use multiple ifs or nest them. Any help hugely appreciated.

    Thanks
    Penn <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IFs can you nest or extend them? (Office 2000)

    You can nest IFs up to 7 times.

    In your case the following should work :

    <big>=IF(C7="Town1",B2,IF(C7="Town2",B3,IF(C7="Tow n3",B4,"")))</big>

    If C7 will only have one of the 3 values you suggested (and is never blank) the following shorter version would work

    <big>=IF(C7="Town1",B2,IF(C7="Town2",B3,B4))</big>

    There is also the possibility of using VLOOKUP (or HLOOKUP) against a table of towns matched to the appropriate value.

    Andrew C

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Oxford, England
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFs can you nest or extend them? (Office 2000)

    Thank You so much - perfect - it works beautifully <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> You have saved my sanity (and my job probably) <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IFs can you nest or extend them? (Office 2000)

    Penn - I cannot recommend too strongly that you look into Andrew's passing comment that you could also use a lookup table. Nested IFs are okay, but they are difficult to maintain - if you need to change the tests (spelling changes, values, outcomes) it is a minor pain, and they are hard for third parties to understand (and a month or so after you use them, that includes YOU!). The other problem is that you can only nest IFs seven levels deep, which can be a maddening constraint if you have eight or nine alternatives to test against.

    Lookups are much easier to maintain, and they make the choices and test/comparison logic very visible - it worth the effort to become familiar with them.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IFs can you nest or extend them? (Office 2000)

    Just want to second Dean's observation that a Lookup table is the best way to go: nested ifs are very hard to read and you are limited to only nesting 7 deep. For a great explanation of the alternatives, see John Walkenbach's alternatives to nested IF functions. For your case, one of the options that I like is the CONCATENATE function:
    <pre>=CONCATENATE(IF(C7="town1",B2,""),IF(C7="town 2",B3,""),IF(C7="town3",B4,""))</pre>

    I've attached a worksheet which shows both the VLOOKUP and CONCATENATE options. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFs can you nest or extend them? (Office 2000)

    When you start having to nest more than 3 or 4 times, you will find that a function is much more efficient and easier to read and write.

    For example,

    =IF(C7="Town1",B2,IF(C7="Town2",B3,IF(C7="Town3",B 4,"")))

    could be replaced with
    =GetTown(C7,B2,B3,B4), provided you have entered the function below into Visual Basic.

    Alt F11 to get over to Visual Basic, Insert Module.
    and type:
    Function Gettown(Value,A,B,C)
    IF Value = "Town1" Then
    Gettown = B2
    ElseIF Value ="Town2" Then
    Gettown = B3
    ElseIF Value = "Town3" Then
    Gettown = B4
    Else
    Gettown = ""
    End If
    End Function

    An improvement to this is to use the Select Case Statement

    Function Gettown(Value,A,B,C)
    Select Case Value
    Case "Town1"
    Gettown = B2
    Case "Town2"
    Gettown = B3
    Case "Town3"
    Gettown = B4
    Case else
    Gettown = ""
    End select
    End Function

Posting Permissions

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