Solved

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

Posted on 2012-04-06
3
385 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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 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