=IF problem

E

EdoZwart

HELP!

as a sort of beginner finaly using the =IF command and is working fine.
BUT.....i try to add some more if commands in one line but it doesn't
work:

=IF(A3="Default Template Windows 2003 Member
Server","DEFAULT-YES",IF(A3="Default Template Windows 2000 Domain
Controller","DEFAULT-YES",IF(A3="Default Template Windows 2003 Domain
Controller","DEFAULT-YES",IF(A3="Default Template Windows 2000 Member
Server","DEFAULT-YES",IF(A3="Default Template Windows 2000 Cluster
Server","DEFAULT-YES",IF(A3="Default Template Windows 2003 Cluster
Server","DEFAULT-YES",IF(A3="Default Template Windows 2000 Member
Server IIS","DEFAULT-YES",IF(A3="Custom","NO"))))))))

Does anyone know what the problem can be or another solution? Is there
a maximum of characters or something?

Thanks in advanced!

Edo
 
P

Pete

The maximum number of nested IF statements is 7 (or 8, depending on how
they are counted). If you have more conditions then the VLOOKUP( )
function can be used in conjunction with a table of available values
and outcomes.

Pete
 
P

PCLIVE

You could use something such as:

=IF(OR(A3="Default Template Windows 2003 Member Server",A3="Default Template
Windows 2000 Domain Controller",A3="Default Template Windows 2003 Domain
Controller",A3="Default Template Windows 2000 Member Server",A3="Default
Template Windows 2000 Cluster Server",A3="Default Template Windows 2003
Cluster
Server",A3="Default Template Windows 2000 Member Server
IIS"),"DEFAULT-YES",IF(A3="Custom","NO"))

This formula will do what you want. However, if A3 does not include one of
the values specified in your formula, then the result will be "False". If
you want it to return something else, such as "No Match", then use the
following.

=IF(OR(A3="Default Template Windows 2003 Member Server",A3="Default Template
Windows 2000 Domain Controller",A3="Default Template Windows 2003 Domain
Controller",A3="Default Template Windows 2000 Member Server",A3="Default
Template Windows 2000 Cluster Server",A3="Default Template Windows 2003
Cluster Server",A3="Default Template Windows 2000 Member Server
IIS"),"DEFAULT-YES",IF(A3="Custom","NO","No Match"))

HTH,
Paul
 
P

Pete

Your function can be simplified, as follows:

=IF(LEFT(A3,7)="Default","DEFAULT-YES",IF(A3="Custom","NO","Not
specified"))

At present you do not specify what should happen if A3 contains any
other values than those in your formula, so this version makes this
clearer.

Pete
 
P

PCLIVE

Pete,

Though your formula is much simpler, it would not be effective if A3
contained "Default Template Windows XP Member Server" or any other data
(other than what Edo specified) that begins with "Default".

The second formula that I provided does, in fact, specify what will happen
when nothing matches the values in the formula. I also explained that the
first formula would return "False" should A3 not match any of the values
specified in the formula. The second formula would return "No Match" when
there was no match.

Regards,
Paul
 
E

EdoZwart

Thanks for al the reply's! I have setup the excel sheet with vlookup an
is working a lot easier
 
Top