Booleans in Array formulas

P

p cooper

eg
N N O O D D D O

and I want to extract those that are NOT "O" or "D"
={NOT(A1:A8="D")} works
={NOT (A1:A8="O")} works

but i can put them together
={(A1:A8="D") + (A1:A8="O"))} gives me an array filled with #value
ive also tried
={AND (A1:A8="D"),(A1:A8="O")}
and as many combination s as you can think of and cant get it. Can it be done ?
 
A

Alfred Dearnley

Hi!

Help me.

I see array formulae used where I can't see why.

For example, in this case, I would go for something like
=IF(AND(A1<>"O",A1<>"D"),1,""), copy down and watch the 1's to pick my not-O
AND not-D items.

It's probably because I've come to array formulae late in life that I revert
to my 1980-style Basic but I sure find it less opaque. Am I missing out on
performance or is it more an issue of style, taste, elegance and fashion
(none of which I lay claim to but to all of which I accord a place in the
world of mathematics - especially elegance, of course).

Alf


| One way (array-entered):
|
| =NOT((A1:A8="D")+(A1:A8="O"))
|
| In article <[email protected]>,
| [email protected] (p cooper) wrote:
|
| > eg
| > N N O O D D D O
| >
| > and I want to extract those that are NOT "O" or "D"
| > ={NOT(A1:A8="D")} works
| > ={NOT (A1:A8="O")} works
| >
| > but i can put them together
| > ={(A1:A8="D") + (A1:A8="O"))} gives me an array filled with #value
| > ive also tried
| > ={AND (A1:A8="D"),(A1:A8="O")}
| > and as many combination s as you can think of and cant get it. Can it be
done
| > ?
 
J

JE McGimpsey

An array formula certainly isn't necessary, but if the target range is
fixed, it can be convenient to have only a single formula.
 
Top