Hi,

I have a sheet that has many columns. If certain columns have None in
a cell I want the sheet to show me an x in a another column. I can get
the formula to work for one column at a time but not multiple. Example:
Cells N2, P2, and M2 may contain None. How do I write the formula to
read those cells and return and an x in a different column if None is
populated in the cells in question.

Thanks in advance.

Eddie.


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=563258

=if(or(a1="",b1="",c1=""),"none","")


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=563258

=if(or(a1="",b1="",c1=""),"none","")


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=563258

Depends if they all need to be populated or just one, so youll need
either

=IF(AND(N2="None",P2="None",M2="None"),"x","")

or

=IF(OR(N2="None",P2="None",M2="None"),"x","")


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=563258

Awesome. Thanks guys. I was pretty close with my formula but left out
one of the ",". Again I thank you for your quick reply.

Eddie.


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=563258

try

=IF(SUMPRODUCT(--(M2:P2="None")),"x","")

If any cells in the range have "None" then "x" will be placed in cell.

Is this what you require?

"punter" wrote:


Hi,

I have a sheet that has many columns. If certain columns have None in
a cell I want the sheet to show me an x in a another column. I can get
the formula to work for one column at a time but not multiple. Example:
Cells N2, P2, and M2 may contain None. How do I write the formula to
read those cells and return and an x in a different column if None is
populated in the cells in question.

Thanks in advance.

Eddie.


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=563258

Hey that works even better. One question about the formula: What is
the meaning of the (-- between the Sumproduct( and the (M2? The formula
works great but I'm wondering what exatctly the (-- does so I can use it
in the future for other tasks.

Thanks

Eddie


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=563258

"punter" wrote in
message ...

Hey that works even better. One question about the formula: What is
the meaning of the (-- between the Sumproduct( and the (M2? The formula
works great but I'm wondering what exatctly the (-- does so I can use it
in the future for other tasks.

The double unary minus forces the values to be numeric, so is useful if you
are starting with values which are logical (TRUE/FALSE) or text.

The first unary minus reverses the sign, and the second reverses it again.
http://www.mcgimpsey.com/excel/variablerate1.html gives an example.
--
David Biddulph