(--(Function))

[Mike Fogleman]

I have seen the use of (--) in formulas on this NG. What is it's purpose and
applications?
Curious to learn..
Mike F

 

[Bob Phillips]

Hi Mike,

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for more
than youn ever thought you needed

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Mike-

This technique was popularized by Harlan Grove in
converting arrays of booleans to 1's and 0's when working
with arrays in formulas. I think it's best explained
through an example. Imagine this list in A1:A5:

eat
easy
tree
elephant
car

We want to count the number of words beginning with the
letter "e". We could use the formula:

=SUMPRODUCT(--(LEFT(A1:A5)="e"))

This formula evaluates the first letter of each cell in
the range and equates it to "e". If you select that
portion of the formula and press F9, you would see:

=SUMPRODUCT(--({TRUE;TRUE;FALSE;TRUE;FALSE}))

Unfortunately, we can't sum these booleans in this way,
so we use the double dash (actually it's referred to as
double unary I believe) to convert the booleans first to
all negatives:

=SUMPRODUCT(-{-1;-1;0;-1;0})

and the second one to convert them back to positive. The
0's are not affected. So you end up with:

=SUMPRODUCT({1;1;0;1;0})

which equals 3.

It should be noted that there are other methods. You can
also multiply by 1, add 0, use the power of 1, or use the
N function to convert the array of booleans.

=SUMPRODUCT(1*(LEFT(A1:A5)="e"))
=SUMPRODUCT(0+(LEFT(A1:A5)="e"))
=SUMPRODUCT(N(LEFT(A1:A5)="e"))
=SUMPRODUCT((LEFT(A1:A5)="e")^1)

Determining which formula is most efficient of all of
these is an argument for another day.

HTH
Jason
Atlanta, GA