IF THEN in worksheet functions and in VBA

Location: http://www.mvps.org/dmcritchie/excel/ifthen.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

IF Worksheet Function (#if)

The IF...THEN is a bit hard to understand in HELP initially for both Worksheet Functions and more so in VBA. Your use of Lotus does not make matters simpler. So the first thing I would do is turn off all Transition Options in Tools, Options, Transition and then remember to use an "=" sign in front of formulas; otherwise, you will not be in synch with other people and will not be able to enter dates in a simple fashion.

Help does not put the = in front of the worksheet formulas, because they can be stacked inside one another, but this can lead to confusion.

Look at the syntax in Excel HELP for IF Worksheet Function
=IF(logical_test,value_if_true,value_if_false)
=IF(logical_test1,value_if_true1,value_if_false1)

Nesting (#nesting)

nesting the FALSE portion only:
=IF(logical_test1,value_if_true1,IF(logical_test2,value_if_true2,value_if_false2))

nesting the TRUE portion only:
=IF(logical_test1, IF(logical_test2,value_if_true2,value_if_false2), value_if_false1)

I added numbers to help make the above more readable. You can plop another IF(test,true,false) into the true action part or the false action part. There is a limit of seven levels of nesting ...
=IF(IF(IF(IF(IF(IF(IF(IF(condition,True,False), ...
by which time you would be pretty much lost anyway if you tried to maintain the formulas.

Chip Pearson has a page on nesting IF statements, don't think that will help as much as the HELP in Excel with the above information. But we try to do things explain a few things better than HELP and may or may not succeed. http://www.cpearson.com/excel/nested.htm
Where Chip's pages shine in this area is a way to overcome the limit of seven levels by using named ranges.

Another alternative to heavy nesting of IF statements may be the VLOOKUP Worksheet Function.

Use of OR Worksheet Function to reduce need for nesting:
OR(E2={5;10;25;100;200;364;500})

Excel Function Dictionary « by Peter Noneley is a reference to definitely download and maintain on your computer, the workbook has 157+ sheets each with an explanation and example of an Excel function.

Better Solutions, Russell Proctor, lots of indexed information on Excel and Word. Links are denoted by color and not by underlining.

IF ... THEN logic in VBA (#ifthen)

IF Statements in VBA present their own set of problems to the uninitiated. There are two forms of the syntax and you can't mix them. Those that fit on one line or are logically on one line.

     IF condition THEN  action;  Else other_action

and those that don't go on one line

     IF  condition  THEN
            action1
            action2
     ElseIf condition Then
            action3 
     Else
            action4
     End IF

in the above since you are using multiple lines you cannot use "Else action4" on one line. Note the difference, VBA is not as free form as many other languages.

Peter Noneley's workbook examples are currently on Ron de Bruin's site, see My Excel Pages home page for a link.
WS examples with =IF(condition,true,false) and with VBA usage of IF,THEN,ELSE can be found on my strings page.
WS examples with heavy nesting, Nested IFs, Chip Pearson explains how it is possible to get past a well known limitation in Excel of 7 nesting levels by using defined names (a bit oomplicated for normal use).
VBA examples using ElseIf can be found in Insert a Row using a Macro to maintain formulas and in Use of Propercase
VBA example of use of the Case Statement in an Event macro to color cells (more tha 3 colors) using a Case statemet.

This page was introduced on July 7, 2001.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

IF THEN in worksheet functions and in VBA

IF Worksheet Function (#if)

Nesting (#nesting)

IF ... THEN logic in VBA (#ifthen)

Related (#related)