I saw an example in another forum that used "--" like this:
=SUMPRODUCT(--($A$2:$A$9=$J$2),--($B$2:$B$9=$K$2),(D$2:D$9))
What does qualifying the array expression with "--" do?
In this case the arrays in the first two expressions (columns A and B) contain text. The formula is seeking a match for the two text columns then returning another column (D) with corresponding data.
I do not have a specific problem to solve but am wondering about this usage.
-- is referred to as double unary coercion
A Boolean True/False can be coerced to it's integer equivalent 1/0 respectively in a number of ways, the most common being:
The double unary is opted for by many as it is deemed as perhaps the more efficient of the options ... (using division is limited to specific situations - ie 1/FALSE generally leads to #DIV/0! errors)
Please Login or Register to view this content.
Some have/do argue for single unary coercion given less operations required to coerce but it means being aware how of how many "arrays" of values are being coerced - ie if the number is odd the result will be returned in the opposite sign to the correct value.
For a good overview of SUMPRODUCT and coercion see the link in my sig. to Bob Phillips' white paper.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Re: Using "--" in a SUMPRODUCT formula
Thanks for a quick reply! I didn't realize explicit conversion was needed when the context used a boolean as an integer. The link was informational too. I have a software development background but coercion policies can vary widely......thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks