Solved

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 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