What Does (--) Mean????

ANSWER FROM OLD POST ....

Question
I ran into this Formula on VBAExpress
=SUMPRODUCT(--($B$2:$B$8=F2),--($D$2:$D$8="X"))
And I was wondering what the dashes '--' do in a formula? I've never come across using
-- in a formula before





ANSWER........

It's called a double unary minus. Minus minus is the same as plus.
It coerces a TRUE/FALSE boolean into 1/0. The same could be achieved
by multiplying by 1 or adding zero. But -- is in vogue at the moment because
apparently it is a nanosecond or two quicker than the alternatives.

It would more likely be written:

=SUMPRODUCT(($B$2:$B$8=F2)*($D$2:$D$8="X"))

It compares each cell in B2:B8 with F2 and returns an array of TRUE/FALSE values.
Then it compares each cell in D2:D8 with "X" and returns another (equally sized) array
of TRUE/FALSE values. The 2 arrays are multiplied together, and in the process TRUE is
coerced to 1 and FALSE to zero. That results in an array of 1/0 values
(1*1=1, 1*0=0, 0*1=0, 0*0=0). This array is then summed. The effect is a
count of the two conditions being TRUE.

SOURCE: An old post , that I kept , from this board. Sorry I can't remember who wrote the post .

Nimrod said:

ANSWER FROM OLD POST ....

Question
I ran into this Formula on VBAExpress
=SUMPRODUCT(--($B$2:$B$8=F2),--($D$2:$D$8="X"))
And I was wondering what the dashes '--' do in a formula? I've never come across using
-- in a formula before





ANSWER........

It's called a double unary minus. Minus minus is the same as plus.
It coerces a TRUE/FALSE boolean into 1/0. The same could be achieved
by multiplying by 1 or adding zero. But -- is in vogue at the moment because
apparently it is a nanosecond or two quicker than the alternatives.
...

Click to expand...

Technically speaking the explanation offered here is wrong if you want to account for the temporal difference that is put in a pejorative mood.

If "[m]inus minus is the same as plus," why is this

+(A2:A4=B)

not working as intended?

Hello,

I assume you meant 'B1', Aladin?

Looking at this, it's a non-operation:

http://office.microsoft.com/en-us/assistance/HP051986941033.aspx

The positive operator is not supported.

Let me add the following:

Two levels:

Generally speaking:
It performs implicit type coercion, coercing a boolean to a double. Booleans (TRUE & FALSE) are internally housed as integers, arithmetically working on them, and flipping the sign bit, will coerce a boolean to a number. TRUE is housed as 1, FALSE is housed as 0.

More details:
--x is to take the additive inverse of the additive inverse of x.

That is:

-x = 0-x

And:

--x = -(-x)

So:

--x = -(-x) = 0-(0-x)

But, since we're using Double data types, we're not calculating arithmetic results, we're actually just flipping the sign bit, a fairly fast operation, twice.

More information:

The Boolean is Special:
http://msdn.microsoft.com/library/en-us/odeopg/html/deconthebooleandatatype.asp

But note, in Excel, True coerces to 1, not -1.

VB Coercion Rules:
http://support.microsoft.com/kb/129803/

Additive Inverse:
http://en.wikipedia.org/wiki/Additive_inverse

IEEE Floating-Point Representation (Double & Sign Bit):
http://msdn.microsoft.com/library/e...nt_Representation_and_Microsoft_Languages.asp