IF question

P

punter

Hi,

I have a sheet that has many columns. If certain columns have None i
a cell I want the sheet to show me an x in a another column. I can ge
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 t
read those cells and return and an x in a different column if None i
populated in the cells in question.

Thanks in advance.

Eddie
 
S

Special-K

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

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

or

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

punter

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

Eddie
 
T

Toppers

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?
 
P

punter

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

Thanks

Eddi
 
D

David Biddulph

punter said:
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.
Click to expand...

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