Solved

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

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
splitting text of cell to columns 14 24
Freeze Panes Solution 6 28
Excel Automation VBA 19 37
Export Query data to excel file 14 36
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now