AND() for an array

Z

Zilla

I thought this would AND cells for several rows
in two columns, and put the result where the formula
is, but it doesn't. What syntax am I missing?

I have a mix of 1s & 0s in cols A and B, rows 1-10. I
expect that if a row in both A and B are 1's, col C will have
TRUE, FALSE otherwise. So my array formula in C is,

=AND(A1:A10, B1:B10)

I get FALSE for all C1:C10.
 
B

Bob Phillips

Because that is not the way AND works. What exactly did you expect, TRUE if
1 is in any of a1:A10, B1:B10, TRUE if A and B is 1 (1 instance or all), or
what.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
T

Teethless mama

Another way....

Select blank cells C1:C10
=A1:A10*B1:B10

ctrl+shift+enter, not just enter
 
B

Bernd

Hello,

Yes, that's it.

Or =and(a1,b1) and copy down.

Or - if the and-function is absolutely necessary:
array-enter
=AND(INDEX(A1:A10,ROW(INDIRECT("1:10"))),INDEX(B1:B10,ROW(INDIRECT("1:10"))))

Regards,
Bernd
 
H

Harlan Grove

Zilla said:
Yes thanks, but why doesn't the AND() work?
....

It does work according to its specs. It's handling your

=AND(A1:A10, B1:B10)

like

=AND(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,
B1,B2,B3,B4,B5,B6,B7,B8,B9,B10)

so like

=AND(AND(A1,AND(A2,AND(A3,AND(A4,A5)))),
AND(A6,AND(A7,AND(A8,AND(A9,A10)))),
AND(B1,AND(B2,AND(B3,AND(B4,B5)))),
AND(B6,AND(B7,AND(B8,AND(B9,B10)))))

That's the way AND is SUPPOSED to work. It's just not how you want it
to work. FWIW, it's no different than being disappointed that
SUM(A1:A10,B1:B10) doesn't produce the same result as A1:A10+B1:B10.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top