Solved

Using ranges (vs. single cell references) in Excel equalities and inequalities

Posted on 2012-04-06
3
393 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

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
 
LVL 50

Accepted Solution

by:
barry houdini earned 300 total points
ID: 37817395
Hello Steve,

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;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

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;0;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
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 200 total points
ID: 37818402
In addition to using the +0, the alternative to convert the TRUE/FALSE result to numeric 1/0's can be done via a *1 or double unary.

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<>"")*(B1:B15<>"Dave"))  would convert to 1/0's already so the +0, *1, or double unary is not needed.


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
0
 

Author Closing Comment

by:Steve_Brady
ID: 37876584
Thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

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.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question