What does -- mean in formulas

[illini_99]

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

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[joeu2004]

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

 

[Peo Sjoblom]

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

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


--


Regards,


Peo Sjoblom

 

[joeu2004]

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

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)