range of cells satisfying a condition

dharsana

Board Regular
Joined
Sep 7, 2002
Messages
58
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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"?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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... :biggrin:
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCD
110FALSE
240
370
421
5k0
6k0
Sheet2
</SPAN>

Regards!

Yogi Anand
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
ABCD
110FALSE
240
370
42
5k0
6k0
Sheet2
</SPAN>

Regards!

Yogi Anand
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,958
Members
416,002
Latest member
t10k14

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top