What does the "--" mean when placed before a function/formula?

[shadestreet]

I have seen people post formulas or functions that have a double minu
sign in front of the formula, what exactly does this do?

Thank

 

[shadestreet]

Ron H answered this nicely in a different thread that just happened t
touch upon the double negatives...

"You're referring to the unary operators.
Essentially the "double minus" coerces TRUE into -1 and then 1.
A FALSE remains 0.
Do a search for "unary" operators on the newsgroup.
There was one good discussion at"

http://tinyurl.com/3d6tk

I admit I am still a bit confused on them, especially what a singl
unary does opposed to double, but this thread got me at least clued in

 

[Domenic]

*I admit I am still a bit confused on them, especially what a singl
unary does opposed to double, but this thread got me at least clue
in. *

The first minus coerces TRUE/FALSE to -1/0.

The second minus negates it to 1/0

 

[RagDyeR]

Simply, it performs a multiplication.
From your high school days, you might remember that two negatives equal a
positive.
So, using a double unary yields a positive result.

Really, *almost* the same result can be attained by using - times one (*1),
but since *not* in every case, it's safer to use it whenever needed.

Why and/or when to use it:

Around here, you'll probably see it used mostly with Sumproduct(), mainly I
believe, because the great minds in this NG have raised Sumproduct to a
level above what Redmond had devised for the function when they created it
in the first place.
No where in XL's help files can you find the kind of results described for
It, as it's used in these forums.

With A1 blank, enter this in B1:

=(A1>0)
You get False.

Type a number in A1.
You get TRUE.

Now, change B1 to this:

=--(A1>0)

And see what happens!

You've converted the formula result to a number, which can now be understood
(used) by Sumproduct *and* various other functions.

In a different vein, but in a similar situation, in Vlookup() and Match(),
where, in the Help files, an exact match needs a "False" argument, and an
approximate one needs a "True" argument, and you see many people use zero
(0) and one (1).

Another example:

In A1 enter,
123ABC

In B1 enter,
=LEFT(A1,3)

In C1 enter,
=SUM(B1)

Notice the 123 in B1 is left justified, meaning it's text.
That's why C1 displays zero (0).

Now change B1 to this,
=--LEFT(A1,3)

And see what happens.


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have seen people post formulas or functions that have a double minus
sign in front of the formula, what exactly does this do?

Thanks

 

[RagDyeR]

Check out this thread for a lot more info.

http://tinyurl.com/3d6tk
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Simply, it performs a multiplication.
From your high school days, you might remember that two negatives equal a
positive.
So, using a double unary yields a positive result.

Really, *almost* the same result can be attained by using - times one (*1),
but since *not* in every case, it's safer to use it whenever needed.

Why and/or when to use it:

Around here, you'll probably see it used mostly with Sumproduct(), mainly I
believe, because the great minds in this NG have raised Sumproduct to a
level above what Redmond had devised for the function when they created it
in the first place.
No where in XL's help files can you find the kind of results described for
It, as it's used in these forums.

With A1 blank, enter this in B1:

=(A1>0)
You get False.

Type a number in A1.
You get TRUE.

Now, change B1 to this:

=--(A1>0)

And see what happens!

You've converted the formula result to a number, which can now be understood
(used) by Sumproduct *and* various other functions.

In a different vein, but in a similar situation, in Vlookup() and Match(),
where, in the Help files, an exact match needs a "False" argument, and an
approximate one needs a "True" argument, and you see many people use zero
(0) and one (1).

Another example:

In A1 enter,
123ABC

In B1 enter,
=LEFT(A1,3)

In C1 enter,
=SUM(B1)

Notice the 123 in B1 is left justified, meaning it's text.
That's why C1 displays zero (0).

Now change B1 to this,
=--LEFT(A1,3)

And see what happens.


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have seen people post formulas or functions that have a double minus
sign in front of the formula, what exactly does this do?

Thanks

 

[Harlan Grove]

Simply, it performs a multiplication.

Simply put, you're wrong.

A single unary minus converts a number to its additive inverse. This isn't
multiplication per se though the same result could be accomplished by
multiplying by -1. And in terms of computer implementation, all digital
representations use a sign bit. Unary minus flips the sign bit between 1 and
0.

 

[Ragdyer]

Somewhere in these forums Harlan, you once told me that I shouldn't be
allowed within 100 meters of a computer running XL because I didn't know
what an odd or even number was.
This might be a similar situation, where I perhaps bend technicality a
little in an attempt to enhance the ease of understanding a particular
operation.

I must submit however, your explanation:
<<"A single unary minus converts a number to its additive inverse">>,
though undoubtedly, probably absolutely correct, is *less* understandable
then my "sophomoric" statements of:
<<"Simply, it performs a multiplication. From your high school days, you
might remember that two negatives equal a positive.">>

The goal here being "Understanding", as opposed to "Technical Accuracy".

One could argue that accuracy is necessary in attaining understanding.
I would take exception to that premise, where understanding overshadows
technical accuracy in a small way, in the same manner as feeding with
fishes, is overshadowed by the teaching of how to fish!

Meanwhile, back at the ranch, I accept your critique, and will attempt in
the future to blend and homogenize my simplistic explanations with more
technical savvy.

 

[Harlan Grove]

I must submit however, your explanation:
<<"A single unary minus converts a number to its additive inverse">>,
though undoubtedly, probably absolutely correct, is *less*
understandable then my "sophomoric" statements of:
<<"Simply, it performs a multiplication. From your high school days,
you might remember that two negatives equal a positive.">>
The goal here being "Understanding", as opposed to "Technical
Accuracy".

....

-1 can't be defined by itself and shouldn't be defined circularly.

-1 = 0 - 1, and the binary operator '-' is defined as the complementary
operator to '+', so that a + b = c -> c - b = a and c - a = b. Unary minus
signs in -x could (and perhaps should) be considered shorthand for 0 - x.
The fact that -x = -1 * x is nothing more than a consequence of how the
multiplicative identity and its additive inverse should work in any
algebraic ring. Negatives as typically denoted by the use of unary minus
signs are entirely well-defined in terms of addition alone.

Then there's the standard computer implementation. Again, all digital
representations of (OK, signed) numbers use a sign bit, and unary minuses in
[very] high level languages merely result in flipping the sign bit. Anyone
who tried implementing them as multiplication by -1 would shortly be looking
for alternative employment better suited to their intellectual capabilities.

So this is somewhat subjective. For you, -x = -1 * x is more understandable
than -x = 0 - x. Fine, but who says anyone else would find it so?

And to understand -x using your approach, wouldn't you already need to know
what -1 means? Consider that -1 = -1 * 1 isn't a clear explanation, but
implicitly it's the one you're giving.

 

[Dana DeLouis]

Hi. I most likely am wrong, but here's two cents just for discussion. I
think it should be mentioned that I think --True is a unique "Feature" of
Excel, and usually would not work in other languages.

I believe it is correct in that "A single unary minus converts a number to
its additive inverse." However, in this context, I believe we are not
working with numbers per say. We are working with a return item from a
Boolean operation. The results of say (5>4) returning True, is not a
number, nor a string. It can not be custom formatted based on its value,
and is not a string ( ie. Custom formatting of >0, <0, 0, string, does not
work with Boolean.)

Excel is unique in that a cell formula such as ="123"+4 returns 127.
Usually, a language will not allow the addition of a string and a number. I
know you can't compare Apples and Oranges, but the program Mathematica I
know would not allow this. So, I think it is unique feature of Excel that
allows -True to return -1. This makes no sense in other languages.
However, this is a useful spreadsheet "Feature." If my memory serves me
correctly, I think this was one of the "nice features" added over Lotus 123?

If you have programmed in other languages, you might recognize the double
"--" as a "PreDecrement" operation.

In other words, in Excel vba, we have to decrement a value with something
like this.

r = r - 1

In faster languages, such as Mathematica and others, it is done like this:

--r

(Note: This is the "PreDecrement" operation. r-- is called just
"Decrement." )

So, doing a PreDecrement on --True, or --(4<5) makes no sense.in other
languages.

You might be interested to know that when the results of an operation is not
a number, a Minus sign is represented in a program like Mathematica via
multiplication.

Both of these operations are represented via multiplication:

- True
- (5 > 4)

Both return

Times[-1,True]

My gut feeling is that Excel is doing the same thing as it brings in the
value of what True. Excel uses basically standard IEEE computer math, so
that is my basis for this gut feeling. (I should mention that the above
really does not make sense (Times[-1,True]) in Mathematica, but will keep it
in case it encounters something like ( -True / True) in which case it will
cancel the True's, and return an answer of -1. )


Also, because "x" is not a number, the "Full Form" of - x is a
multiplication.

-x//FullForm

Times[-1,x]


You may find the following two implementations of computer math interesting.
Here we put a hold on the evaluation, and view it's internal structure. We
see that getting from 2 to -2 is done via multiplication. Note the "-3" via
unary operation that was mentioned since most computer subtraction is done
via Addition.

-(5-3)//HoldForm//FullForm

Times[-1,Plus[5,-3]]

Even the following -(5), because of the ( ), is multiplication.

-(5)//HoldForm//FullForm

Times[-1,5]

And of course, -5 is just -5 via simple unary minus conversion.

Anyway, very interesting subject. Again, I know you can't compare the two
programs, but it does offer a glimpse into how computer math is done.
Dana DeLouis

 

[Tushar Mehta]

It's a way to force a conversion from a text or boolean value to a
numeric result. The same could be accomplished with 0+ or 1* but some
people like to show off.

Oh, and the rationalization will be something along the lines of 'unary
operator' 'operator precedence' 'speed' (like something that is all of
-- what? --3.14159 nanoseconds faster than 0+ matters on even the
slowest PC in 2004) or some such.

Here are some serious drawbacks of double-minus. It is a part of the
written (English) language. So, if someone writes 'Enter the
following: B1--"12", C1--"13", D1--"14"' what does the person want you
to do? Also, some/many programs will convert a double-minus to a em-
dash (or is it a en-dash?). Talk about confusion.

It would be great if XL supported true conversion functions such as
CStr() CNum(), CBool() and, maybe, CDate() -- but unfortunately, it
doesn't.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

Hi. I most likely am wrong, but here's two cents just for discussion.
I think it should be mentioned that I think --True is a unique
"Feature" of Excel, and usually would not work in other languages.

There are few other languages that have a separate binary type. Pascal (and
its derivatives) is the only one I can think of, and it's sooooo 'correct'
that no arithmetic operator of any kind is allowed to work with them.
FWIW, -- works with awk to force numeric strings to be treated as numbers.
That was necessary in an old MKS version of awk I used years ago, but hasn't
been needed in gawk 3.x.

I believe it is correct in that "A single unary minus converts a
number to its additive inverse." However, in this context, I believe
we are not working with numbers per say. . . .

Semantics. Applying any arithmetic operator to a boolean value coerces that
value into either a 1 (for TRUE) or 0 (for FALSE) *first*. Once converted to
a number, the operand is used in conjunction with the operator to give the
arithmetic result. So, once you use a boolean in an arithmetic context, it
ceases to be a boolean and becomes a number by virtue of automatic boolean
to number conversion.

Excel is unique in that a cell formula such as ="123"+4 returns 127.

No, the same thing can be accomplished in any scripting language you could
name, and I believe (but can't find out for sure) that this also works in D
and Icon. You may be right if you just mean that other spreadsheets don't
allow this (other than those designed to work similar to Excel).

. . . So, I think it is unique feature of Excel that
allows -True to return -1. This makes no sense in other languages.

Because many other languages weren't screwed up in the original design by
having a separate boolean type, other than Pascal & offspring. C and its
derivative languages represent TRUE and 1 and FALSE as 0, though any nonzero
numeric value is treated as true when used in a boolean context.

More semantics. In C,

int n;
n = -(1 == 3 - 2);

sets n to -1. So what are you trying to say?

However, this is a useful spreadsheet "Feature." If my memory serves
me correctly, I think this was one of the "nice features" added over
Lotus 123?

....

Correct, in 123 +"123"+4 would return ERR.

You might be interested to know that when the results of an operation
is not a number, a Minus sign is represented in a program like
Mathematica via multiplication.

Both of these operations are represented via multiplication:

- True
- (5 > 4)

Both return

Times[-1,True]

And the relevance of Mathematica to Excel is?

Mathematica is meant to do symbolic math, and there are reasons why it may
be efficient to translate unary minus as multiplication. In languages and
systems that do their arithmetic immediately in binary, unary minus is
represented as flipping the sign bit.

My gut feeling is that Excel is doing the same thing as it brings in
the value of what True. Excel uses basically standard IEEE computer
math, so that is my basis for this gut feeling. . . .

....

You seem to be saying that you believe Excel is using multiplication because
it uses IEEE binary floating point. Why do you believe IEEE binary floating
point has anything to do with how unary minuses are implemented at lower
level?

The Intel i386 Pentium FPUs are all presumably IEEE-754 compliant. Those
FPUs use the machine instruction FCHS to change the sign of values held in
floating point registers. Do you believe Excel uses something else (not that
it'd surprise me given all the oddities in Excel)?

Anyway, very interesting subject. Again, I know you can't compare the
two programs, but it does offer a glimpse into how computer math is
done.

No. It gives a glimpse into how Mathematica performs symbolic math, which
has different requirements than immediate binary numeric calculation. With
regard to Excel, it's likely irrelevant and misleading.

 

[Harlan Grove]

It's a way to force a conversion from a text or boolean value to a
numeric result. The same could be accomplished with 0+ or 1* but some
people like to show off.

Of course.

Oh, and the rationalization will be something along the lines of
'unary operator' 'operator precedence' 'speed' (like something that
is all of -- what? --3.14159 nanoseconds faster than 0+ matters on
even the slowest PC in 2004) or some such.

So, do you have anything against the operator precedence aspect? I'll grant
that since Excel's + and * operators both have left to right associativity
the chances of typos using +0 or *1 are remote, but a leading -- is
guaranteed to work only on its single operand. If that don't float your
boat, fine for you.

Here are some serious drawbacks of double-minus. It is a part of the
written (English) language. . . .

So are periods, but most people can figure out that 1.5 isn't one sentence
ending in 1 and the next beginning with 5.

So, if someone writes 'Enter the following: B1--"12", C1--"13",
D1--"14"' what does the person want you to do?

Without leading equal signs in Excel, looks like they want you to enter some
literal text. Then again, is one asks how-to questions, why would it not be
imperative to follow responses literally?

Also, some/many programs will convert a double-minus to a em-
dash (or is it a en-dash?). Talk about confusion.

But Excel doesn't, does it. Gosh, different programs do different things
with the same keystrokes! Horrors!!

It would be great if XL supported true conversion functions such as
CStr() CNum(), CBool() and, maybe, CDate() -- but unfortunately, it
doesn't.

Ignorance really isn't bliss. What do you get from =N(1<2) and =N(1>2)? What
about DATEVALUE("1-Jun-2004")?

As for CSTR and CNUM, given Excel's otherwise reasonable (at least to this
awk programmer) string-number conversion semantics, who needs 'em?

 

[Tushar Mehta]

Great post! Hopefully, others will read it and -- more important --
pause to think about its implications.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi. I most likely am wrong, but here's two cents just for discussion. I
think it should be mentioned that I think --True is a unique "Feature" of
Excel, and usually would not work in other languages.

{snip}

 

[JE McGimpsey]

Comments in-line...

It's a way to force a conversion from a text or boolean value to a
numeric result. The same could be accomplished with 0+ or 1* but some
people like to show off.

Why ascribe a pejorative motivation, Tushar? "Show off" implies an
emotional component that I don't understand. Speaking for myself, I
don't have nearly the emotional investment that you seem to...<g>

Why is 0+ and 1* somehow less show-off-y? They all have the same number
of characters (though being different characters, 0+ and 1* take
slightly longer to type than --, both requiring use of the shift key on
the regular US keyboard).

Oh, and the rationalization will be something along the lines of 'unary
operator' 'operator precedence' 'speed' (like something that is all of
-- what? --3.14159 nanoseconds faster than 0+ matters on even the
slowest PC in 2004) or some such.

Operator precedence is a real thing. Is there a good rationale for using
a combination of lower precedence? Is there a good rationale for using a
slower operation, even if it's only pi nanoseconds?

Here are some serious drawbacks of double-minus. It is a part of the
written (English) language.

No, it's really not. When one used to use a typewriter, two hyphens were
perforce used to signify an em-dash, but I haven't had to worry about
that since about 1984.

So, if someone writes 'Enter the
following: B1--"12", C1--"13", D1--"14"' what does the person want you
to do?

Well, if they're Excel savvy, they want you to add 12 or 13 or 14 to B1
or C1 or D1. If they're not, who knows? It's improper English usage.

Also, some/many programs will convert a double-minus to a em-
dash (or is it a en-dash?). Talk about confusion.

Not XL, though. "Some" programs convert =Sum(c) to =Sum<copyright
symbol> too... Oh wait, XL does by default! Now *that* is confusing!

 

[Harlan Grove]

There are few other languages that have a separate binary type. . . .

Make that boolean type.

FWIW, -- works with awk to force numeric strings to be treated as numbers. . .

...

No, it doesn't. It has to be written as -(-"123").

No, the same thing can be accomplished in any scripting language you could
name, and I believe (but can't find out for sure) that this also works in D
and Icon. You may be right if you just mean that other spreadsheets don't
allow this (other than those designed to work similar to Excel).

...

Not so wide open in scripting languages. It works in awk, perl, rexx, tcl and
VBScript (so presumably VJScript) but not in python or ruby.

Mathematica is meant to do symbolic math, and there are reasons why it may
be efficient to translate unary minus as multiplication. In languages and
systems that do their arithmetic immediately in binary, unary minus is
represented as flipping the sign bit.

...

Further to this, one of Mathematica's key operations is reduction of terms,
which means grouping things that offset each other. Handling unary minus as
multiplication by -1 allows for subsequent simplification. There's no remotely
comparable need or purpose served in Excel.

 

[Harlan Grove]

Great post! Hopefully, others will read it and -- more important --
pause to think about its implications.

Oh, so if expression X exists in Excel but not in some other languages, X should
be avoided out of some misguided sense of using the lowest common denominator of
available features in the widest set of languages? This would argue for using
POWER and CONCATENATE because some languages lack ^ and & operators or
equivalents. Would it also argue for never using more than 7 levels of nested
function calls in other languages because that *input* feature isn't available
in Excel?

'--' in C-derived languages is a decrement operator. In Octave and SciLab, which
have adopted C's increment and decrement operators, --1 is a syntax error. In
other languages, it's two unary minuses in sequence. In R (so presumably also in
S), --1 gives 1. In MathCAD, --1 evaluated in floating point becomes 1. In APL,
--1 returns 1. It's all a question of the language's syntax. You may prefer not
to use what you consider exotic syntax in Excel or other languages, but why is
it such a good idea to limit your use of any language?

As for comparison with Mathematica, what makes sense in symbolic math is not
much more relevant to spreadsheets than what makes sense in regular expressions
to process text. Indeed, there are certain aspects of how Mathematica works that
are closer to regular expression engines than spreadsheets.