Vector Compare to Scalar

Question

Is it possible to compare a vector of values to a single scalar value in Excel?

Example:

=IF(A1:Z1 > 0, 1, 0)

Typically, I've seen the following:

=IF( AND( A1>0, B1>0, C1>0, . . . Y1>0, Z1>0 ), 1, 0)

score 4 · Accepted Answer · answered Feb 10 '16 at 19:16

4

How about

=IF(COUNTIF(A1:Z1,">0")=COUNT(A1:Z1),1,0)

or

=IF(COUNTIF(A1:Z1,">0")=COUNTA(A1:Z1),1,0)

or

=IF(COUNTIF(A1:Z1,">0")=COLUMNS(A1:Z1),1,0)

depending on which works best in your situation.

answered Feb 10 '16 at 19:16

Engineer Toast

Adam · Answer 2 · 2016-02-10T22:13:03.503

4

Although this doesn't address the generalized question, for your particular example wouldn't this do the trick?

=IF( Min(A1:Z1) > 0, 1, 0)

If the smallest one is greater than 0, then they all are. Otherwise there is at least one that is not greater than 0.

edited Feb 10 '16 at 22:13

answered Feb 10 '16 at 21:12

Adam

1

It's amazing what people come up with when they think about things. :-) – fixer1234 Feb 10 '16 at 21:16
1

This is *definitely* simpler than mine for OP's use case. +1 – Engineer Toast Feb 11 '16 at 16:16

2 Answers2