ALL and ANY functions?

[Atreides]

I am used to programming with the ALL and ANY logical function. Do they exist
in Excel?

e.g. ALL(range,"y") = TRUE when range = "y","y","y","y"

but ALL(range,"y") = FALSE when range = "y","y","n","y"

and

ANY(range,"y") = FALSE when range = "n","n","n","s"

but ANY(range,"y") = TRUE when range = "n","y",'n","s"

I think I can program this using COUNTIF but a simpler way would be nice.

Thanks

Atreides

 

[Mike H]

Hi,

Countif doesn't seem to onerous to me

=COUNTIF($A$1:$A$5,"y")=5 for All
=COUNTIF($A$1:$A$5,"y")>0 for Any

Mike

 

[Ron Rosenfeld]

I am used to programming with the ALL and ANY logical function. Do they exist
in Excel?

e.g. ALL(range,"y") = TRUE when range = "y","y","y","y"

but ALL(range,"y") = FALSE when range = "y","y","n","y"

and

ANY(range,"y") = FALSE when range = "n","n","n","s"

but ANY(range,"y") = TRUE when range = "n","y",'n","s"

I think I can program this using COUNTIF but a simpler way would be nice.

Thanks

Atreides

Closest is probably the array-entered formulas:

=AND(range="y")

=OR(range="y")

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.
--ron

 

[Atreides]

Thanks Ron!

I like this suggestion the best because it's very simple. Using COUNTIF
requires you to enter the length of the array.

Atreides