Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Become a Premium Member and unlock a new, free course in leading technologies each month.

Solved

Posted on 2012-04-06

Hello,

On some occasions, I see excel formulas containing equalities or inequalities (e.g. >, <, <>, etc.) which include ranges rather than single cell references. I am comfortable with the single cell references such as:

=B1<>""

but I'm puzzled by those containing ranges:

=B1:B15<>""

What exactly does that mean? Does it mean that at least one cell in the range is not blank or does it mean that all cells in the range are not blank? Or does it mean something else entirely?

If anyone can provide an explanation or insights regarding this concept (or point me to a location that does), I would be very grateful.

Thanks

On some occasions, I see excel formulas containing equalities or inequalities (e.g. >, <, <>, etc.) which include ranges rather than single cell references. I am comfortable with the single cell references such as:

=B1<>""

but I'm puzzled by those containing ranges:

=B1:B15<>""

What exactly does that mean? Does it mean that at least one cell in the range is not blank or does it mean that all cells in the range are not blank? Or does it mean something else entirely?

If anyone can provide an explanation or insights regarding this concept (or point me to a location that does), I would be very grateful.

Thanks

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

- Help others & share knowledge
- Earn cash & points
- Learn & ask questions

3 Comments

The first of those, i.e.

=B1<>""

simply produces a single "logical" result, TRUE or FALSE, but with the latter the result is an "array", =B1:B15<>"" will result in this sort of array

={TRUE;FALSE;FALSE;FALSE;F

The first, seventh and tenth entries are TRUE, indicating that B1, B7 and B10 have values in them but the rest don't

If you put that formula in a single cell you won't see the whole array as the result, but you can use it within another formula, so changed to this

=(B1:B15<>"")+0

that will return 1s instead of TRUE and zeroes instead of FALSE, i.e.

={1;0;0;0;0;0;1;0;0;1;0;0;

so if you put that in SUMPRODUCT, for instance, you'd get a sum of those like this

=SUMPRODUCT((B1:B15<>"")+0

that will return 3 for my example

regards, barry

e.g.,

=SUMPRODUCT((B1:B15<>"")*1

or

=SUMPRODUCT(--(B1:B15<>"")

However, neither is necessary if something else is being multiplied by the array, including another TRUE/FALSE array.

e.g.,

=SUMPRODUCT((B1:B15<>"")*(

PS, if you evaluate...

=B1:B15<>""

by selecting the formula and hitting F9, you can see what the result looks like. I do this alot with pieces of my formula to understand the results and how they come together and use this approach rather than Excel's formula evaluate as I can follow what I'm doing more carefully than seeing how Excel evaluates and I sometimes want to evaluate pieces in the order that interests me than in the order Excel evaluates.

Dave

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Earn Certification

HTML5 Specialist - Certification

Free withPremium

Course of the Month6 days, 11 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.