Check if any value in a range is equal to 0

's Computer Specifications

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

's Computer Specifications

=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
>
>

's Computer Specifications

This should work

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

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

's Computer Specifications

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","Valid")
>
> 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
>
>

's Computer Specifications

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","Valid")
> >
> > 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
> >
> >

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
A macro to check range and then check another range if falset-then	Jim A	Microsoft Excel Discussion	2	13th Jan 2009 06:09 PM
How to check for a date range match in one column and then count thevalues equal to in another	Jon	Microsoft Excel Worksheet Functions	5	18th Jan 2008 11:54 AM
VBA check to see if variable value is odd but not equal to 1	=?Utf-8?B?RGF2ZSBM?=	Microsoft Excel Misc	4	21st Nov 2007 01:54 AM
Can i use If to check if a cell is equal to any value in a particular range	manan	Microsoft Excel Discussion	1	25th May 2006 09:51 AM
Check to see Row Counts are equal	CPK	Microsoft Access	2	18th Feb 2005 02:04 PM