Solved

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

Posted on 2012-04-06
3
364 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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