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)

=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.

and those that don't go on one lineIFconditionTHENaction;Elseother_action

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.IFconditionTHENaction1 action2ElseIfconditionThenaction3Elseaction4End IF

- 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*

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