is there an "if for each"?
 

Hello,
I'm trying to do a 'check' formula. There is a column of data I want to
report on in a separate cell. In the case where all the data is the
same....say each cell in the column contains "1", is there an if-then formula
that I can use to get:

(in excel-ese:)

=if(each cell in column x = 1,good,bad)

Many thanks,
MJ

is there an "if for each"?
 

Try a formula like

=IF(COUNTIF(A1:A3,1)=COUNT(A1:A3),TRUE,FALSE)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"M John" wrote in message
...
Hello,
I'm trying to do a 'check' formula. There is a column of data
I want to
report on in a separate cell. In the case where all the data
is the
same....say each cell in the column contains "1", is there an
if-then formula
that I can use to get:

(in excel-ese:)

=if(each cell in column x = 1,good,bad)

Many thanks,
MJ

is there an "if for each"?
 

Normally, in a "check" situation you would know how many items you are
checking, so you should be able to use, presuming bad is 0:

=if(sum(X:X) = 25,"good","bad")

"M John" wrote:

Hello,
I'm trying to do a 'check' formula. There is a column of data I want to
report on in a separate cell. In the case where all the data is the
same....say each cell in the column contains "1", is there an if-then formula
that I can use to get:

(in excel-ese:)

=if(each cell in column x = 1,good,bad)

Many thanks,
MJ

is there an "if for each"?
 

Perfect. Most appreciated.

"Chip Pearson" wrote:

Try a formula like

=IF(COUNTIF(A1:A3,1)=COUNT(A1:A3),TRUE,FALSE)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"M John" wrote in message
...
Hello,
I'm trying to do a 'check' formula. There is a column of data
I want to
report on in a separate cell. In the case where all the data
is the
same....say each cell in the column contains "1", is there an
if-then formula
that I can use to get:

(in excel-ese:)

=if(each cell in column x = 1,good,bad)

Many thanks,
MJ

is there an "if for each"?
 

I hesitate to correct Chip, but you should use COUNTA
instead of COUNT if the cells aren't guaranteed to contain
numbers.

For a terser (but equivalent) version, try
=COUNTIF(A1:A3,1)=COUNTA(A1:A3)

or
=AND(A1:A3=1)
entered as an array formula (Ctrl-Shift-enter)

(I've just noticed that if any of the cells contain error values then
only my first formula above gives the correct answer)


Andrew Taylor



M John wrote:
Perfect. Most appreciated.

"Chip Pearson" wrote:

Try a formula like

=IF(COUNTIF(A1:A3,1)=COUNT(A1:A3),TRUE,FALSE)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"M John" wrote in message
...
Hello,
I'm trying to do a 'check' formula. There is a column of data
I want to
report on in a separate cell. In the case where all the data
is the
same....say each cell in the column contains "1", is there an
if-then formula
that I can use to get:

(in excel-ese:)

=if(each cell in column x = 1,good,bad)

Many thanks,
MJ