# Thread: Nested If Statements (Excel 97)

1. ## 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. ## 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

3. ## Re: Nested If Statements (Excel 97)

Joanne

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

=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

4. ## 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

5. ## Re: Nested If Statements (Excel 97)

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

Joanne

6. ## 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.

7. ## 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.

8. ## 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.

9. ## 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
•