Using "--" as an array formula shortcut / alternative

[trippknightly]

Just today I discovered that you can do some cool things w/
conditional sums of the form:

sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000)

The above might give me sales in the Western region of Blue things
(column c might have the sales figures). This appears to avoid having
to enter arrary formulas (CTRL SHIFT ENTER as well as the likely
different specification for the formula itself) and would seem to be a
lot easier to work with.

What I haven't seen documented other than quick allusions to it is
exactly what the "--" is doing. Since "--" isn't easy to search for
on the web, I thought somebody here might be able to describe or refer
me to a documentation source online that describes what "--" (&
wrapping w/ () ) is doing. Seems to be a shorthand way to indicate
"do an array operation with this".

Thx!,

- TK

 

[Ron Coderre]

In the case of boolean expressions (that return TRUE/FALSE), the dbl-neg
converts TRUE to 1 and FALSE to 0.

Example:
--ISNUMBER(56) converts TRUE to the number 1
--ISNUMBER("this is text") converts FALSE to the number 0

You could achieve the same results by multiplying a value by 1, but the
dbl-neg indicates to knowledgable users that a "type conversion" is being
effected.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ollie4]

so what does
sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000)
deferrer from
sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1:C1000))?

 

[Bob Phillips]

It's called a double unary. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[trippknightly]

In the case of boolean expressions (that return TRUE/FALSE), the dbl-neg
converts TRUE to 1 and FALSE to 0.

Example:
--ISNUMBER(56) converts TRUE to the number 1
--ISNUMBER("this is text") converts FALSE to the number 0

You could achieve the same results by multiplying a value by 1, but the
dbl-neg indicates to knowledgable users that a "type conversion" is being
effected.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)







- Show quoted text -

I'm glad I asked. Got it. It's so obvious now that I see what it
is. Thanks for the lighting reply.

 

[T. Valko]

Using the "--" is slightly more efficient.

Try this:

A1 = west
B1 = blue
C1 = 100

Enter both of your sample formulas and they both return 100. Now, change C1
to "X" and see what happens.

 

[Bob Phillips]

Imagine a table with

A1: Region
B1: Colour
C1: Amount
A2: West
B2: Blue
C2: 100

now run both formulae against that data.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[trippknightly]

so what does
sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000)
deferrer from
sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1:C1000))?

Related question:

Why does the "*" work, but if you use "," instead you don't get the
right answer. I thought sumproduct multiplied the entries so in a
way, I thought they'd still get multiplied "the right way".

PS, thanks for the link Bob Phillips.

 

[Ollie4]

Wow, tnx for the link! I'm using sumproduct for allmost everything for
the last years, but never knew the -- trick. Great! Tnx! Now i dont
have to validate the data first.

 

[Bob Phillips]

* works where comma doesn't when there are conditions. So

=SUMPRODUCT(A1:A10,B1:B10)

works fine when they are all numbers. But if you want to test for A1:A10 >
10 say the test

A1:A10>10 returns TRUE FALSE which have to be transformed into numbers for
SUMPRODUCT to work. One way is to multiply the arrays (TRUE*1=1, FALSE*1=0),
another is to double negate it (--TRUE), another is to add 0 (TRUE+0),
another is to raise to the 0th power (TRUE^2). All of these have the effect
of transforming to a number which SUMPRODUCT is happier to work with.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

so what does
sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000)
deferrer from
sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1:C1000))?

Related question:

Why does the "*" work, but if you use "," instead you don't get the
right answer. I thought sumproduct multiplied the entries so in a
way, I thought they'd still get multiplied "the right way".

PS, thanks for the link Bob Phillips.