What does -- mean in formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

illini_99 - 29 Nov 2007 15:23 GMT

I have seen formulas that include two -'s. I am not sure what this does. Is
it just a way to add some spacing since two negatives equal a positive?

Example:
=SUMPRODUCT(--(A1:A100=1),--(B1:B100<>""))

Reply to this Message

Peo Sjoblom - 29 Nov 2007 15:28 GMT

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

Signature

Regards,

Peo Sjoblom

>I have seen formulas that include two -'s. I am not sure what this does.
>Is
> it just a way to add some spacing since two negatives equal a positive?
>
> Example:
> =SUMPRODUCT(--(A1:A100=1),--(B1:B100<>""))

Reply to this Message

joeu2004 - 29 Nov 2007 16:02 GMT

On Nov 29, 7:23 am, illini_99 <illini...@discussions.microsoft.com>
wrote:

> I have seen formulas that include two -'s. I am not sure what this does. Is
> it just a way to add some spacing since two negatives equal a positive?
> Example:
> =SUMPRODUCT(--(A1:A100=1),--(B1:B100<>""))

Well, -A1 simply means "negative of A1"; so if A1 is 2, -A1 is -2. "--
A1" means "negative of negative of A1", which does not change the
value of A1; for example --2 is 2.

The question should be: why is that needed in the formula above?

The answer is: in order to treat the boolean results as integers.
For example, (A1:A100=1) results in the value TRUE or FALSE. Those
are encoded as 1 and 0 respectively. But they are not recognized as
integers unless they are used in arithmetic expression such as "--
expression".

The "--" could have been avoided by coding the formula as follows,
with the same effect:

=SUMPRODUCT((A1:A100=1)*(B1:B100<>""))

Reply to this Message

Peo Sjoblom - 29 Nov 2007 16:20 GMT

> On Nov 29, 7:23 am, illini_99 <illini...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 20 lines]
>
> =SUMPRODUCT((A1:A100=1)*(B1:B100<>""))

But there is a difference, the former uses SUMPRODUCT's built in way of
dealing with the arrays thus if you for instance use

=SUMPRODUCT((A1:A100=1)*(B1:B100<>"")*(C1:C100))

vs.

=SUMPRODUCT(--(A1:A100=1),--(B1:B100<>""),C1:C100)

to SUM what's in C and if the values in C can contain text like "" derived
from formulas the former will throw an error while the latter will SUM the
values ignoring any text blanks

Signature

Regards,

Peo Sjoblom

Reply to this Message

joeu2004 - 29 Nov 2007 19:35 GMT

> "joeu2004" <joeu2...@hotmail.com> wrote in message
> > The "--" could have been avoided by coding the formula as follows,
[quoted text clipped - 10 lines]
> to SUM what's in C and if the values in C can contain text like "" derived
> from formulas the former will throw an error

I should have written ``__in_this_case__ "--" could have been
avoided``. And perhaps you should have written "__sometimes__ there
is a difference".

I don't believe there is a difference in the OP's example. And I
would have written your counter-example correctly as:

=SUMPRODUCT((A1:A100=1)*(B1:B100<>""),C1:C100)

Perhaps the counter-example you were struggling to think of is:

=SUMPRODUCT(--(A1:A100=1),C1:C100)

I agree that there are circumstances where it is incorrect to replace
that with:

=SUMPRODUCT((A1:A100=1)*C1:C100)

Reply to this Message