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 email 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
email: "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:
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.
Figure A 

This formula returns 1, 2, 3, or 4, depending on the values in column A. 
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.
Share the spreadsheet wealth 
We've just scratched the surface of the benefits of using nested function calls in formulas. If you'd like to share your favorite spreadsheet tip, please post a comment below or send us a note. 
Copyright © 19992001 TechRepublic, Inc.
Created Date: 01/18/2002 Last Reviewed: 01/18/2002 Rev. Date: