Using an Array within AND/OR Functions

Array {} can't contain functions or operators. It can be only string or numeric values.

So...

Please Login or Register  to view this content.

Works, but not.

Please Login or Register  to view this content.

Basically whatever is inside array is treated as either literal string or numeric value.

Hi,

Interesting question (for a change! ).

Yes - such operators are invalid when employed as such. You would need to use e.g. COUNTIF:

=AND(COUNTIF(I2,{">=2","<=5"}))

Regards

If you literally say what you're trying to express, you'll understand the reason this fails in the syntax you have it: I2 is EQUAL to LESS THAN or EQUAL to 2, or I2 is EQUAL to GREATER THAN or EQUAL to 5.

Something like COUNTIF would be needed.

Originally Posted by CK76

Array {} can't contain functions or operators. It can be only string or numeric values.

Functions, no, but operators, yes, when employed within the IF(S) family of functions.

Regards

It can when employed within the IF(S) family of functions.

True, but it's IF(s) family of function's treatment of literal string that makes the difference I believe.

Below isn't valid syntax, for an example.

Please Login or Register  to view this content.

But below is.

Please Login or Register  to view this content.

Originally Posted by CK76

True, but it's IF(s) family of function's treatment of literal string that makes the difference I believe.

Agreed.

Originally Posted by CK76

Below isn't valid syntax, for an example.

Please Login or Register  to view this content.

Going slightly off-topic now, but just to show that we can generate the necessary construction with a little work:

=SUMPRODUCT(COUNTIFS(B2,{">","<"}&D1:E1))

Regards

Can you give a practical example of what you'd like to achieve over such a large dataset?

Since both the straight comparison and the COUNTIF construction must each process two clauses, I wouldn't have thought there'd be much difference between the two. Though I haven't tested this.

Regards