Nested If Statements (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Joanne

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

Here is your If statement:

=IF(E14,E19,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

Reply | Quote

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.

Reply | Quote

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:

=IF(A2>=25,5,IF(A2>=10,4,IF(A2>=5,3,IF(A2>=1,2,0))))

is what you want, assuming that the years are in cell A2. HTH –Sam

Reply | Quote

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

Joanne

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote