A quick lesson in nested IF tests

Mar 3, 2000
Jeff Davis
© 2002 TechRepublic, Inc.

In "Two tips for translating codes into meaningful terms in Excel," I demonstrated a practical application for Excel's Choose and Vlookup functions. Since that article posted, I've received a number of e-mail messages asking for help in composing formulas to do specific tasks.

It struck me that many Excel users don't know how to use one of the most basic spreadsheet functions of all—the If function—so here's a quick tutorial in how to "nest" If tests. (We veteran spreadsheet jockeys have been using this one for years.)

If...then...else; repeat
Here's a problem that's typical of the requests many of you submitted via e-mail: "Do you have any information on doing If...then...else type formulas? I can't seem to find this info in the Help area. I need to write a formula that returns 1, 2, 3, or 4, depending on the value in another cell."

One solution, of course, is to use a set of nested If tests. Here are the details of that request:

• If A1 contains a value of 10 or less, then we want to put 1 in B1.
• If A1 is between 11 and 50, then put 2 in B1.
• If A1 is between 51 and 99, then put 3 in B1.
• If A1 is greater than 100, then put 4 in B1.

In order to return the correct result, enter this formula in cell B1:
=IF(A1<11,1,IF(A1<51,2,IF(A1<100,3,4)))

When you copy that formula down column B, it will automatically return the correct number, depending on the values in column A. Figure A shows what our sample results look like.

How does this formula work?
This formula starts out looking like a typical "if" test, which takes the form:
=IF(expression,true_result,false_result)

If expression evaluates to be true, the function returns true_result. Otherwise, the function returns false_result. So the function =IF(1>0,"of course","no") returns "of course," because the expression 1>0 is always true.

To arrive at our solution, we used additional If tests instead of values or strings for the true and false results. This process is called nesting one or more functions within another. So, in our function
=IF(A1<11,1,IF(A1<51,2,IF(A1<100,3,4)))

the first If test is A1<11, the second test is A1<51, and the third is A1<100. Excel evaluates the If tests from left to right, and doesn't return a value until it "hits" an expression that evaluates to true.

Copyright © 1999-2001 TechRepublic, Inc.

Created Date: 01/18/2002  Last Reviewed: 01/18/2002  Rev. Date: