range of cells satisfying a condition

[dharsana]

hi,
This should be a simple one but I am unable to do it.
I have a range of cells which contains a formula.These cells diplay 0 value as a formula result.I want to have a formula in another cell which sholud return a value(say "true")if all the cells in the range shows zero value.Even if one cell contains other than zero then the result shuold return false.
pl help

dharshana

 

[Barry Katcher]

Easy way but not foolproof:

=IF(SUM(your range of cells)=0,"true","false")

Careful, though. If you have positive and negative numbers in the cells AND THEY NET OUT TO ZERO, you'll get a "true".

 

[Aladin Akyurek]

On 2002-10-27 13:09, dharsana wrote:
hi,
This should be a simple one but I am unable to do it.
I have a range of cells which contains a formula.These cells diplay 0 value as a formula result.I want to have a formula in another cell which sholud return a value(say "true")if all the cells in the range shows zero value.Even if one cell contains other than zero then the result shuold return false.
pl help

dharshana

How about an empty cell when you state: "Even if one cell contains other than zero then the result shuold return false"?

 

[Barry Katcher]

Aladin, it's Sunday. What the hell are we doing on here?

 

[Aladin Akyurek]

On 2002-10-27 13:30, Barry Katcher wrote:
Aladin, it's Sunday. What the hell are we doing on here?

That's a hard one...

 

[Yogi Anand]

Hi dharsana:

see following worksheet simulation wherein I have used the formula array-based formula

'=IF(SUM(IF(ISNUMBER(B1:B6),IF(B1:B6=0,1,0)))=MATCH(9.99E+307,B:B)-ROW(A1)+1,TRUE,FALSE)

y021027h1.xls
	A	B	C	D
1	1	0		FALSE
2	4	0
3	7	0
4	2	1
5	k	0
6	k	0
Sheet2

</SPAN>

Regards!

Yogi Anand

 

[Yogi Anand]

Hi dharsana:

And if one of the cells in the range of interest is empty, it will return a FALSE, it will return TRUE only if all of the cells of interest house only 0

y021027h1.xls
	A	B	C	D
1	1	0		FALSE
2	4	0
3	7	0
4	2
5	k	0
6	k	0
Sheet2

</SPAN>

Regards!

Yogi Anand

 

[Aladin Akyurek]

There must be a correlation between my posing a question and "worksheet simulations" flying in.

Well, I'll just assume that a formula returned blank or simply an empty cell must be considered also as reason for returning FALSE. If so, there is no need for an additional column and an "array-based" formula...

=ROWS(A1:A5)=COUNTIF(A1:A5,0)

will suffice. Adjust the range to suit.

 

[Yogi Anand]

On 2002-10-27 14:21, Aladin Akyurek wrote:

There must be a correlation between my posing a question and "worksheet simulations" flying in.

Well, I'll just assume that a formula returned blank or simply an empty cell must be considered also as reason for returning FALSE. If so, there is no need for an additional column and an "array-based" formula...

=ROWS(A1:A5)=COUNTIF(A1:A5,0)

will suffice. Adjust the range to suit.

Hi Aladin:

Your formula is beautiful -- short and sweet! I took a long convoluted route to do the same thing. Thank You.

Regards!

Yogi Anand