I am trying to write an IF statement to look for a number in either one row
or columb and then return a value if the statement is true. I am able to get
everything to work until I try putting in my ninth IF statement and then I
get a message that tells me I am trying something wrong. Are you only
allowed to have eight nested statements? Here is an example of what I can
get to work:

=IF(A1=1,"",IF(A2=1,"",IF(A3=1,"",IF(A4=1,"",IF(A5 =1,"",IF(A6=1,"",IF(A7=1,"",IF(A8=1,"",1))))))))

I would really like get that ninth IF in their, or is their another way of
doing this?

I checked out the vlookup and lookup statements, and either I don't know
what or how to write them, or they just aren't what I am looking for.

Thank you for any help on this.

ROD

Hi!

Try this:

=IF(ISNUMBER(MATCH(1,A1:A9,0)),"",1)

You can only nest 7 levels. That means the top level plus 7 for a total of
8.

Biff

"rmitchell87" wrote in message
...
I am trying to write an IF statement to look for a number in either one row
or columb and then return a value if the statement is true. I am able to
get
everything to work until I try putting in my ninth IF statement and then I
get a message that tells me I am trying something wrong. Are you only
allowed to have eight nested statements? Here is an example of what I can
get to work:

=IF(A1=1,"",IF(A2=1,"",IF(A3=1,"",IF(A4=1,"",IF(A5 =1,"",IF(A6=1,"",IF(A7=1,"",IF(A8=1,"",1))))))))

I would really like get that ninth IF in their, or is their another way of
doing this?

I checked out the vlookup and lookup statements, and either I don't know
what or how to write them, or they just aren't what I am looking for.

Thank you for any help on this.

ROD

In this instance, one play is to use:

=IF(SUMPRODUCT(--(A1:A8=1))=0,1,"")

(normal ENTER will do)

Just extend the range covered to suit,
e.g. to cover the range A1:A20, put in say, B1:

=IF(SUMPRODUCT(--(A1:A20=1))=0,1,"")

But do note that we can't use entire col references
(e.g.: A:A, B:B, etc) in SUMPRODUCT
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"rmitchell87" wrote in message
...
I am trying to write an IF statement to look for a number in either one
row
or columb and then return a value if the statement is true. I am able to
get
everything to work until I try putting in my ninth IF statement and then I
get a message that tells me I am trying something wrong. Are you only
allowed to have eight nested statements? Here is an example of what I can
get to work:


=IF(A1=1,"",IF(A2=1,"",IF(A3=1,"",IF(A4=1,"",IF(A5 =1,"",IF(A6=1,"",IF(A7=1,"
",IF(A8=1,"",1))))))))

I would really like get that ninth IF in their, or is their another way of
doing this?

I checked out the vlookup and lookup statements, and either I don't know
what or how to write them, or they just aren't what I am looking for.

Thank you for any help on this.

ROD