Excel - Double Negatives (Past, Present and Future)
 

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.

Excel - Double Negatives (Past, Present and Future)
 

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)


--
Regards
Frank Kabel
Frankfurt, Germany


TKT-Tang wrote:
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.

Excel - Double Negatives (Past, Present and Future)
 

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

"Frank Kabel" wrote in message
...
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)


--
Regards
Frank Kabel
Frankfurt, Germany


TKT-Tang wrote:
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.

Excel - Double Negatives (Past, Present and Future)
 

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

Excel - Double Negatives (Past, Present and Future)
 

Bernie Deitrick wrote:
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.

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

Excel - Double Negatives (Past, Present and Future)
 

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)

--
Regards
Frank Kabel
Frankfurt, Germany


DennisE wrote:
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

Excel - Double Negatives (Past, Present and Future)
 

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.



In article ,
(DennisE) wrote:

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.

Excel - Double Negatives (Past, Present and Future)
 

Hi Denise!

"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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"DennisE" wrote in message
...
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

Excel - Double Negatives (Past, Present and Future)
 

[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.


Hi JE

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

Frank

Excel - Double Negatives (Past, Present and Future)
 

In article ,
"Frank Kabel" wrote:

Probably a Google search with the criteria '--'.

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.

Excel - Double Negatives (Past, Present and Future)
 

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)

"JE McGimpsey" wrote in message
...
In article ,
"Frank Kabel" wrote:

Probably a Google search with the criteria '--'.

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.

Excel - Double Negatives (Past, Present and Future)
 

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?


--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
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)


--
Regards
Frank Kabel
Frankfurt, Germany


TKT-Tang wrote:
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.

Excel - Double Negatives (Past, Present and Future)
 

Tom Ogilvy wrote:
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?


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

Excel - Double Negatives (Past, Present and Future)
 

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

"Bob Phillips" skrev i melding
...
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)

"JE McGimpsey" wrote in message
...
In article ,
"Frank Kabel" wrote:

Probably a Google search with the criteria '--'.

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.

Excel - Double Negatives (Past, Present and Future)
 

"Tom Ogilvy" wrote...
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?
...

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.

--
To top-post is human, to bottom-post and snip is sublime.

Excel - Double Negatives (Past, Present and Future)
 

"DennisE" wrote...
. . . I would classify doing such things as being among the poorest
programming practices ever seen. . . .

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

--
To top-post is human, to bottom-post and snip is sublime.

Excel - Double Negatives (Past, Present and Future)
 

Thanks for the response.

--
Regards,
Tom Ogilvy

"Harlan Grove" wrote in message
...
"Tom Ogilvy" wrote...
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?
..

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.

--
To top-post is human, to bottom-post and snip is sublime.

Excel - Double Negatives (Past, Present and Future)
 

No, but it is still a big leap from avoiding -- to spotting unary minus.

Bob

"Harald Staff" wrote in message
...
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

"Bob Phillips" skrev i melding
...
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)

"JE McGimpsey" wrote in message
...
In article ,
"Frank Kabel" wrote:

Probably a Google search with the criteria '--'.

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.

Excel - Double Negatives (Past, Present and Future)
 

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

But my question is when does this become faster?

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



Tom Ogilvy wrote:

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?

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
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)


--
Regards
Frank Kabel
Frankfurt, Germany


TKT-Tang wrote:
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.


--

Dave Peterson

Excel - Double Negatives (Past, Present and Future)
 

"Dave Peterson" wrote...
....
But my question is when does this become faster?

=abs(sumproduct(-(),-(),-(),-())
....

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.

Excel - Double Negatives (Past, Present and Future)
 

I trust your guess.

Thanks,

Harlan Grove wrote:

"Dave Peterson" wrote...
...
But my question is when does this become faster?

=abs(sumproduct(-(),-(),-(),-())
...

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.

--

Dave Peterson

Excel - Double Negatives (Past, Present and Future)
 

In article ,
Harlan Grove wrote:

Wrapping boolean expressions inside N() is another alternative, possibly
clearer, but it eats a nested function call level, so I don't use it.

Also, IsNumber(N(...)) always returns True; that was my first try to get
around the fact that IsNumber(Left(...,2)) always returned False, though the
first two digits of that string was sometimes a number. Eventually I did
run across a usage of IsNumber(-Left(...,2)), which accomplished the
discrimination I wanted (between 1-digit and 2-digit leading numerics in a
date-derived string).

By the way, in Excel XP, IsDate("50-1") returns True; the above code was
originally part of a workaround for that problem.

--
Randy Hudson