signs -- used in formulas

shodan said:

What I often see in a sumproduct formula are these -- signs. eg:

=SUMPRODUCT(--(B1:B25="B");--(C1:C25="S20");A1:A25)

Click to expand...

As I read in some post, I guess that they turn true into 1 and false into 0. Is this correct, and what are they exactly? Is this only to use with sumproduct than? I thought that the if statement always gives 0 or 1 by default?

Can some help me understand please.
Tx

Click to expand...

Yes, they coerce.

The condition: B1:B25="B" will return TRUE or FALSE, which are not suitable as is for multiplication.

They must be coerced to their numeric 1/0 equivalents.

Any mathematical operator - +0, *1, -- - will produce this result.

The can be used in any logical statement, not just SUMPRODUCT.

IF statements yield TRUE and FALSE. To prove this, enter "X" in A1. In B1, enter: =IF(A1="A",1)

In C1 enter =A1="X"

In D1 enter: =A1="A"

shodan

B1:B25="B" returns TRUE or FALSE for each of the 25 elements in the array, same for C1:C25="S20" and they are not automatically converted to 1 or 0.

SUMPRODUCT is used to multiply corresponding NUMBERS in arrays and and add the results. Therefore the TRUEs and FALSEs need to be converted to 1s and 0s and this is done by performing some arithmetic operation on them. The -- does that by negating it and then negting the result again. So the -- is not a special symbol, but just two minus signs.

So --TRUE is effectively --1 (which = 1)
and --FALSE is effectively --0 (which = 0)

Hope I've explained that correctly.

Edit: But even if I did, just_jon has done it faster.

thanks all, makes sense and made it clear, but,.. if every operator gives the same result, why using -- in stead of just + ? is this a way of guideline to be followed within formulas?

shodan

The + by itself does not do anything to the TRUE or FALSE but +0 (or *1) would. However, as I understand it -- is more efficient than the other two methods.

Somewhat extended:

http://www.mrexcel.com/board2/viewtopic.php?t=73205

That the double negation is a bit faster is due to the way the operator precedence is implemented.