Check if any value in a range is equal to 0
 

Hi all.

Quick question.

I have several cells - i.e M8 : M12. These cells can contain integers 0
to 10.

I want to be able to design a formula that says - if any cell in the
range M8:M12 contains a 0 then change the value of M13 to say
"INVALID", otherwise it is "VALID".

I'm having a bit of trouble designing this - tried nest IF statements
and formula.

Suggestions appreciated.

Regards
Daz

Check if any value in a range is equal to 0
 

=IF(COUNTIF(M8:M12,0), "Invalid", "Valid")

"Daz" wrote:

Hi all.

Quick question.

I have several cells - i.e M8 : M12. These cells can contain integers 0
to 10.

I want to be able to design a formula that says - if any cell in the
range M8:M12 contains a 0 then change the value of M13 to say
"INVALID", otherwise it is "VALID".

I'm having a bit of trouble designing this - tried nest IF statements
and formula.

Suggestions appreciated.

Regards
Daz

Check if any value in a range is equal to 0
 

This should work

=IF(OR(M8=0,M9=0,M10=0,M11=0,M12=0),"Invalid","Val id")

Chad


Daz wrote:
Hi all.

Quick question.

I have several cells - i.e M8 : M12. These cells can contain integers 0
to 10.

I want to be able to design a formula that says - if any cell in the
range M8:M12 contains a 0 then change the value of M13 to say
"INVALID", otherwise it is "VALID".

I'm having a bit of trouble designing this - tried nest IF statements
and formula.

Suggestions appreciated.

Regards
Daz

Check if any value in a range is equal to 0
 

If you are open to a suggestion, I think you could shorten it to
=IF(OR(M8:M12=0),"Invalid","Valid")

array entered with Cntrl+Shift+Enter (or you get #VALUE).


"Chad" wrote:

This should work

=IF(OR(M8=0,M9=0,M10=0,M11=0,M12=0),"Invalid","Val id")

Chad


Daz wrote:
Hi all.

Quick question.

I have several cells - i.e M8 : M12. These cells can contain integers 0
to 10.

I want to be able to design a formula that says - if any cell in the
range M8:M12 contains a 0 then change the value of M13 to say
"INVALID", otherwise it is "VALID".

I'm having a bit of trouble designing this - tried nest IF statements
and formula.

Suggestions appreciated.

Regards
Daz

Check if any value in a range is equal to 0
 

Thanks for these very helpful suggestions.


JMB wrote:
If you are open to a suggestion, I think you could shorten it to
=IF(OR(M8:M12=0),"Invalid","Valid")

array entered with Cntrl+Shift+Enter (or you get #VALUE).


"Chad" wrote:

This should work

=IF(OR(M8=0,M9=0,M10=0,M11=0,M12=0),"Invalid","Val id")

Chad


Daz wrote:
Hi all.

Quick question.

I have several cells - i.e M8 : M12. These cells can contain integers 0
to 10.

I want to be able to design a formula that says - if any cell in the
range M8:M12 contains a 0 then change the value of M13 to say
"INVALID", otherwise it is "VALID".

I'm having a bit of trouble designing this - tried nest IF statements
and formula.

Suggestions appreciated.

Regards
Daz