Excel - Double Negatives (Past, Present and Future)

T

TKT-Tang

Am interested to search for exemplary formulae incorporating
minus-minus signs thereof. However, deploying search criterion such as
"--" ain't gotten no good return from the archives. Please show the
ways to search in-situ.

Also, would like to request enlightenment on situations whereby Double
Negatives would be aptly applied in formulae.

Regards.
 
F

Frank Kabel

Hi
one of the common usages is to convert boolean values into numbers
(TRUE = 1, FALSE = 0)
e.g. the formula
=--FALSE = 0
=--TRUE = 1

often used within the SUMPRODUCT function to coerce the boolean values:
=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

Another usage is to convert numbers which are returned from string
function to real numbers.
e.g. the function
=LEFT("1ABS",1)
will return 1 as STRING. You can't use this return in calculations
Though the formula
=--LEFT("1ABS",1)
will return a value.

In this case it replaces the function VALUE(string)
 
B

Bernie Deitrick

TKT,

Note that

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

can also be written

=SUMPRODUCT((A1:A100="A")*(B1:B100="B"))

The multiplication of the two Booleans results in numeric values that can be
summed.

Not sure about the speed differences, and usually, don't care, since this
isn't the sort of formula that I would use many at a time. Pivot tables
would then be the preferred method.

HTH,
Bernie
MS Excel MVP
 
D

DennisE

As far as I can tell, Excel invariably compiles a double minus sign as a plus
sign (and a triple minus sign as a minus, etc.). Thus if A1=4 and B1=3,
C1=A1--B1 yields 7 and D1=A1---B1 yields 1. Although there is nothing illegal
about it, I would classify doing such things as being among the poorest
programming practices
ever seen. It's no wonder a google search for such things turned up nothing.

-- Dennis Eisen
 
F

Frank Kabel

Bernie said:
TKT,

Note that

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

can also be written

=SUMPRODUCT((A1:A100="A")*(B1:B100="B"))

The multiplication of the two Booleans results in numeric values that
can be summed.

Not sure about the speed differences, and usually, don't care, since
this isn't the sort of formula that I would use many at a time.
Pivot tables would then be the preferred method.
Click to expand...

Hi Bernie
according to some discussion the first one should be a little bit
faster. Though to be honest never noticed a difference on my worksheets
:)

Frank
 
F

Frank Kabel

Hi Dennis
I agree with you that the usage of '--' as a replacement for '+' is no
good programming style. But the double unary operator has it's usage
(see my other post)
 
J

JE McGimpsey

You're not understanding operator precedence, nor how Excel evaluates an
expression. Excel doesn't "compile" anything, it evalutes each term in a
formula.

--a

is not evaluated as

+a

it's evaluated as

-(-a)

Look at these results:

= --TRUE ==> 1
= +TRUE ==> TRUE

The first unary minus coerces the boolean value to -1 for TRUE and 0 for
FALSE. The second unary minus coerces the -1 to 1.

You need to reevaluate your classification...

I also don't know what terms you used for your google search. Using
"unary minus" in the Excel group archives turned up 88 results, the
first in 1997, many of them discussing the relative merits of using two
unary minuses vs. other techniques.
 
N

Norman Harker

Hi Denise!

Re: "I would classify doing such things as being among the poorest
programming practices ever seen. It's no wonder a google search for
such things turned up nothing."

See the following thread for a discussion of this:

http://tinyurl.com/372zc

There are cases where it is essential; see especially the post of
Harlan Grove in that thread.

There are also other cases where we are parsing text:

A1:
Autumn2004

=RIGHT(A1,4)
Returns 2004 as text
=--RIGHT(A1,4)
Returns 2004 as a number

And you'll find that Google will throw up hundreds of threads. But
best results will be obtained if you search on "coerce" which is
usually why we are using --.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

[snip]
I also don't know what terms you used for your google search. Using
"unary minus" in the Excel group archives turned up 88 results, the
first in 1997, many of them discussing the relative merits of using
two unary minuses vs. other techniques.
Click to expand...

Hi JE

Probably a Google search with the criteria '--'.
And this indeed will not return useful results :)

Frank
 
J

JE McGimpsey

Frank Kabel said:
Probably a Google search with the criteria '--'.
Click to expand...

Probably - Google's so good that most of the time you don't have to
understand very much about how it works. Having an understanding,
however, is sometimes the difference between finding 100,000 mostly
irrelevant links, or finding the 6 that are really helpful.
 
B

Bob Phillips

But with his misunderstanding of operator precedence and his not knowing how
the -- has been used, it is stretching things to expect him to be looking up
unary minus in Google.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

for this specific usage, wouldn't

=SUMPRODUCT(-(A1:A100="A"),-(B1:B100="B"))

Achieve the same and be even "faster" than

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

Surprised Mr. Grove or Aladan haven't raised this issue - or have they?
 
F

Frank Kabel

Tom said:
for this specific usage, wouldn't

=SUMPRODUCT(-(A1:A100="A"),-(B1:B100="B"))

Achieve the same and be even "faster" than

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

Surprised Mr. Grove or Aladan haven't raised this issue - or have
they?
Click to expand...

Not that I remember :)
But IMHO the 'small' performance benefit is not worth the problems you
may encounter if you add a third condition and forget changing '-' to
'--'

Frank
 
H

Harald Staff

There's a -- above your signature .. and every signature. Can't expect
anyone to do a meaningful Google search on that from a math point of view,
100 000 to 6 is a far too optimistic ratio.

Best wishes Harald
 
H

Harlan Grove

for this specific usage, wouldn't

=SUMPRODUCT(-(A1:A100="A"),-(B1:B100="B"))

Achieve the same and be even "faster" than

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="B"))

Surprised Mr. Grove or Aladan haven't raised this issue - or have they?
Click to expand...
...

Not directly, but you won't find me using ISNUMBER(--whatever) rather than
ISNUMBER(-whatever). I just didn't make the explicit point in previous responses
in which I've done this.

When you know you've got an even number of conditions, you're correct that using
single unary minuses would be better, but in general it's safer to use double
unary minuses. Now, as I've written before, it's not the speed of double unary
minuses I like, it's the fact that due to Excel's operator precendence it's
harder to screw up double unary minuses with typos than it is to screw up the
alternatives ^1, *1, +0. Also, since I read left to right, I prefer my number
type coersions on the left rather than the right of my boolean expressions, and
-- looks nicer than 1* or 0+. Wrapping boolean expressions inside N() is another
alternative, possibly clearer, but it eats a nested function call level, so I
don't use it.
 
H

Harlan Grove

. . . I would classify doing such things as being among the poorest
programming practices ever seen. . . .
Click to expand...

That's probably because you've never programmed in C or its offspring. There's a
certain satisfaction to knowing whether

x---y

evaluates as (x--)-y or x-(--y). That and a long & honorable tradition of
lauding obfuscated code that approached (but can never surpass) the opacity of
hand-crafted APL.

If --x did nothing, as +x does, then I'd agree it should be avoided. The sad
fact is that it does do something, and that something is often necessary even
though it appears to be as pointless as raising to the 1 power, multiplying by 1
or adding 0 (or converting numbers to text, appending "").
 
T

Tom Ogilvy

Thanks for the response.

--
Regards,
Tom Ogilvy

Harlan Grove said:
...
..

Not directly, but you won't find me using ISNUMBER(--whatever) rather than
ISNUMBER(-whatever). I just didn't make the explicit point in previous responses
in which I've done this.

When you know you've got an even number of conditions, you're correct that using
single unary minuses would be better, but in general it's safer to use double
unary minuses. Now, as I've written before, it's not the speed of double unary
minuses I like, it's the fact that due to Excel's operator precendence it's
harder to screw up double unary minuses with typos than it is to screw up the
alternatives ^1, *1, +0. Also, since I read left to right, I prefer my number
type coersions on the left rather than the right of my boolean expressions, and
-- looks nicer than 1* or 0+. Wrapping boolean expressions inside N() is another
alternative, possibly clearer, but it eats a nested function call level, so I
don't use it.
Click to expand...
 
D

Dave Peterson

I think there have a few that did the math (odd/even parms).

But my question is when does this become faster?

=abs(sumproduct(-(),-(),-(),-())
 
H

Harlan Grove

Dave Peterson said:
But my question is when does this become faster?

=abs(sumproduct(-(),-(),-(),-())
Click to expand...
....

Compared to =SUMPRODUCT(--(),--(),--(),--())? I don't know, but I'd guess
it'd take quite a number of double unary minuses to equal one function call.
 
Top