is there an "if for each"?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M John - 28 Feb 2006 14:15 GMT

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

Reply to this Message

Chip Pearson - 28 Feb 2006 14:24 GMT

Try a formula like

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

Signature

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

> Hello,
> I'm trying to do a 'check' formula. There is a column of data
[quoted text clipped - 11 lines]
> Many thanks,
> MJ

Reply to this Message

M John - 28 Feb 2006 14:37 GMT

Perfect. Most appreciated.

> Try a formula like
>
[quoted text clipped - 15 lines]
> > Many thanks,
> > MJ

Reply to this Message

Andrew Taylor - 28 Feb 2006 15:21 GMT

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

> Perfect. Most appreciated.
>
[quoted text clipped - 24 lines]
> > > Many thanks,
> > > MJ

Reply to this Message

Antonio Elinon - 28 Feb 2006 14:32 GMT

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

> Hello,
> I'm trying to do a 'check' formula. There is a column of data I want to
[quoted text clipped - 8 lines]
> Many thanks,
> MJ

Reply to this Message