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

Nesting (#nesting)

nesting the FALSE portion only:

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:

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
     ElseIf condition Then
     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.
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

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved