double dash in formulas --

T

Ted Metro

Sometimes I'll see a dash-dash used in a formula like -

--trim
or
--left

What does that double-dash do when it's infront of the function, and why use
'--trim' instead of just 'trim'?

Ted
 
D

Dave Peterson

=Trim() will return a string (not a number). (Same with =right(), =left(),
=mid()).

So if you had 1234ASDF in a cell and wanted to extract the character 1, you
could use:

=left(a1,1)
But this formula returns the string '1 (like entering a value with a leading
apostrophe).

One way to convert that string 1 to a number 1 is to multiply by 1.
=left(a1,1)*1
or add 0
=left(a1,1)+0

Or even using --
=--left(a1,1)
The rightmost minus would convert it to -1 and the left minus would change the
sign back to positive.

===
You may have seen this in formulas like:

=sumproduct(--(a1:a10="hi"),--(b1:b10="there"))

In this case, the right minus converts True/Falses to -1/0's and the left minus
converts the -1/0 to +1/0.

(and sumproduct likes to work with numbers--not true/false's.)
 
J

Jason Morin

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
 
P

packat

This is a neat trick!!!!!

Dave said:
=Trim() will return a string (not a number). (Same with
=right(),
=left(), =mid()).

So if you had 1234ASDF in a cell and wanted to extract the
character
1, you could use:

=left(a1,1)
But this formula returns the string '1 (like entering a
value with a
leading apostrophe).

One way to convert that string 1 to a number 1 is to
multiply by 1.
=left(a1,1)*1
or add 0
=left(a1,1)+0

Or even using --
=--left(a1,1)
The rightmost minus would convert it to -1 and the left
minus would
change the sign back to positive.

===
You may have seen this in formulas like:

=sumproduct(--(a1:a10="hi"),--(b1:b10="there"))

In this case, the right minus converts True/Falses
to -1/0's and the
left minus converts the -1/0 to +1/0.

(and sumproduct likes to work with numbers--not
true/false's.)
Click to expand...
 
Top