Excel - Double Negatives (Past, Present and Future)

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

Regards.

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

Note that

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" <frank...@freenet.de> wrote in message
news:OtpVI5#FEHA...@TK2MSFTNGP10.phx.gbl...

-- Dennis Eisen

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

--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 <20040401084947...@mb-m21.aol.com>,

Re: "I would classify doing such things as being among the poorest

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
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"DennisE" <den...@aol.com> wrote in message
news:20040401084947...@mb-m21.aol.com...

Hi JE

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

Frank

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.

--

HTH

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

"JE McGimpsey" <jemcg...@mvps.org> wrote in message
news:jemcgimpsey-820B...@msnews.microsoft.com...

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

Achieve the same and be even "faster" than

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

--
Regards,
Tom Ogilvy

news:OtpVI5%23FEH...@TK2MSFTNGP10.phx.gbl...

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

Best wishes Harald

"Bob Phillips" <bob.ph...@notheretiscali.co.uk> skrev i melding
news:OA%23JPm$FEHA...@TK2MSFTNGP11.phx.gbl...

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.

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

--
Regards,
Tom Ogilvy

"Harlan Grove" <hrl...@aol.com> wrote in message
news:1p%ac.379$H4...@www.newsranger.com...

Bob

"Harald Staff" <inno...@enron.invalid> wrote in message
news:uq3QQSCG...@TK2MSFTNGP11.phx.gbl...

But my question is when does this become faster?

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

--

Dave Peterson
ec3...@msn.com

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.

Thanks,

--

Dave Peterson
ec3...@msn.com

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