Thread: =IF problem

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

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

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

"EdoZwart" <EdoZwart.212pvz_1136316601.4478@excelforum-nospam.com> wrote in
message news:EdoZwart.212pvz_1136316601.4478@excelforum-nospam.com...
>
> 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
>
>
> --
> EdoZwart
> ------------------------------------------------------------------------
> EdoZwart's Profile:
> http://www.excelforum.com/member.php...o&userid=30000
> View this thread: http://www.excelforum.com/showthread...hreadid=497646
>

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

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

"Pete" <pashurst@auditel.net> wrote in message
news:1136396155.238786.103930@g44g2000cwa.googlegroups.com...
> 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
>

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