double dash in formulas --
 

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

Hi Ted,

See JE McGoimpsey's explanation at:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards,
Norman



"Ted Metro" wrote in message
...
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

=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.)



Ted Metro wrote:

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

--

Dave Peterson

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

-----Original Message-----
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
.

This is a neat trick!!!!!

Dave Peterson wrote:
=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.)



Ted Metro wrote:

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