Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested If Statements (Excel 97)

    Sorry to post what is probably very simple. I rarely use Excel and can't seem to find an answer.

    I have a column (let's say "Years of Service") and if a number in this column is between 1 and 4, I want to put a 2 in the Vacation Entitlement column. If Years of Service is between 5 and 9, I want to put a 3 in Vacation Entitlement; if it is between 10 and 24, I want a 4 and greater than or equal to 25, I need a 5.

    I am pretty sure I can nest these if statements but I am just not getting the syntax right.

    Any help would be great.

    Joanne

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Nested If Statements (Excel 97)

    Joanne

    Instread of using Nested Ifs, very slow, how about using something like:

    =CHOOSE(A1,2,2,2,2,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,4 ,4,4,4,4,5,5,5,5,5).

    Maybe this an be written shorter, but it would work for you. The choose function looks at the value in A1 and depending on the value it will pick the value from the list. You may want to make these values as text such as in:

    =CHOOSE(A1,"2","2","2","2","3","3","3","3","3","4" ,"4","4","4","4","4","4","4","4","4","4","4","4"," 4","4","5","5","5","5","5")

    So if A1 is 9 then Choose will return the 9th element which would be the last 3.

    HTH.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Nested If Statements (Excel 97)

    Joanne

    Sorry I goofed... I did not see the Greater than Less than stuff.

    Here is your If statement:

    =IF(E1<5,2,IF(AND(E1>4,E1<10),3,IF(AND(E1>9,E1<25) ,4,5)))

    The Choose would work but not for all values. it is neat, and it can do a lot of neat things, but... Maybe you can use it for other things. Check the Help file...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

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

    Re: Nested If Statements (Excel 97)

    Not so simple, unless you are a logician. It usually works best to take the cases backward. It makes for easier reading & eliminates the need for ANDs:<pre>=IF(A2>=25,5,IF(A2>=10,4,IF(A2>=5,3,IF(A 2>=1,2,0))))</pre>

    is what you want, assuming that the years are in cell A2. HTH --Sam
    <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>

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested If Statements (Excel 97)

    Thank you so much SammyB and Wassim! You saved me a lot of hunting time.

    Joanne

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested If Statements (Excel 97)

    A method I use to get the syntax right is to always complete the "skeleton" of the IF before writing any of the "guts". For example, type:

    equal sign
    IF
    opening bracket
    comma
    comma
    closting bracket

    Then go back and fill in the blanks. Doing it this way makes it easier to get the right number of commas and brackets. In this case, the first term would be the comparison (a2>=25), the second term would be the value (5), and the third term would be another IF statement, which you would build the same way. Continue for each additional IF.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    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 If Statements (Excel 97)

    Choose can work nicely for this problem:

    =CHOOSE(1+(A1<=0)+(A1<5)+(A1<10)+(A1<25),5,4,3,2,0 )

    I use this structure frequently.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Nested If Statements (Excel 97)

    Hi,
    FWIW, I'd probably use a LOOKUP function for this. That way if the Vacation Entitlement rules change, you only have to change your lookup table, not all your formulae. I'm attaching a simple spreadsheet as it's easier than describing it!
    HTH.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested If Statements (Excel 97)

    Thanks Rory and Jacksonmacd!

    The time that this board has saved me is incredible and I learned just what I needed to know.

    Rory: The lookup feature is great and I will use it. For this project though, we are just using the Excel file to merge into a bunch of Word documents.

    Thanks again for taking the time to answer my question.

    Joanne

Posting Permissions

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