USing -- in formula

[chelseaaa]

What does -- stand for in the formulas below:

=SUMPRODUCT(--(C1:C100="flag"),A1:A100,B1:B100)

=IF(E13<>"",SUMPRODUCT('Detail Page'!$G$24:$G$83,'Detail Page'!$I$24:$I$83,--('Detail Page'!$E$24:$E$83=D13))/(SUMPRODUCT('Detail Page'!$G$24:$G$83, --('Detail Page'!$E$24:$E$83=D13))),"")

 

[MrKowz]

The -- coerces the conditional statement within the parentheses into a 1 or a 0. By doing this, it creates an array of 1s and 0s. It does this by taking each TRUE/FALSE statement and multiplying it by -1 twice.


Lets take the following data for example:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #FFFFFF;;">Date</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #FFFFFF;;">Amount</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #FFFFFF;;">Type</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Branch</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">4000</td><td style="background-color: #FFFFFF;;">Deposit</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">East</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">6000</td><td style="background-color: #FFFFFF;;">Deposit</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">East</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">2000</td><td style="background-color: #FFFFFF;;">Withdrawl</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">East</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">3000</td><td style="background-color: #FFFFFF;;">Deposit</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">West</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #FFFFFF;;">12/2/2010</td><td style="text-align: right;background-color: #FFFFFF;;">5000</td><td style="background-color: #FFFFFF;;">Withdrawl</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">East</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #FFFFFF;;">12/2/2010</td><td style="text-align: right;background-color: #FFFFFF;;">5000</td><td style="background-color: #FFFFFF;;">Withdrawl</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">West</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFFFF;;">12/3/2010</td><td style="text-align: right;background-color: #FFFFFF;;">10000</td><td style="background-color: #FFFFFF;;">Deposit</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">West</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">12/3/2010</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">4000</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">Withdrawl</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">East</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

And we want to use this SUMPRODUCT function to find the number of Deposits at the East Branch:
=SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”))


What the SUMPRODUCT first does (before it evaluates the double unary -- operator), is internally look at each item on a TRUE/FALSE basis:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #FFFFFF;;">Date</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #FFFFFF;;">Amount</td><td style="font-weight: bold;text-align: center;text-decoration: underline;background-color: #FFFFFF;;">Type</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Branch</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">4000</td><td style="text-align: right;background-color: #FFFFFF;;">TRUE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">6000</td><td style="text-align: right;background-color: #FFFFFF;;">TRUE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">2000</td><td style="text-align: right;background-color: #FFFFFF;;">FALSE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #FFFFFF;;">12/1/2010</td><td style="text-align: right;background-color: #FFFFFF;;">3000</td><td style="text-align: right;background-color: #FFFFFF;;">TRUE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #FFFFFF;;">12/2/2010</td><td style="text-align: right;background-color: #FFFFFF;;">5000</td><td style="text-align: right;background-color: #FFFFFF;;">FALSE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #FFFFFF;;">12/2/2010</td><td style="text-align: right;background-color: #FFFFFF;;">5000</td><td style="text-align: right;background-color: #FFFFFF;;">FALSE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFFFF;;">12/3/2010</td><td style="text-align: right;background-color: #FFFFFF;;">10000</td><td style="text-align: right;background-color: #FFFFFF;;">TRUE</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">12/3/2010</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">4000</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">FALSE</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">TRUE</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

The first condition: (C2:C9=”Deposit”) returns the array {TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}

The second condition: (D2:D9=”East”) returns the array
{TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE}


Since TRUE/FALSE holds values of 1/0, we multiply them by -1 twice (using the double unary operator) to convert them to a positive, numerical, value:

--(TRUE) = (-1)(-1)(TRUE) = (-1)(-1) = 1
--(FALSE) = (-1)(-1)(FALSE) = (-1)(0) = 0

So now, SUMPRODUCT has two arrays to look at:

{1,1,0,1,0,0,1,0} and {1,1,0,0,0,0,0,0}

Following the nature of the SUMPRODUCT formula, it now multiplies these two arrays together and adds those products:

1*1 + 1*1 + 0*0 + 1*0 + 0*0 + 0*0 + 1*0 + 0*0 = 1+1+0+0+0+0+0+0 = 2 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

 

[Magriza]

Further reading:
http://www.mrexcel.com/forum/showthread.php?t=70547
http://www.mrexcel.com/forum/showthread.php?t=128907
http://www.mrexcel.com/forum/showthread.php?t=202204
http://www.mrexcel.com/forum/showthread.php?t=215623