"joeu2004" wrote in message
...
On Nov 29, 7:23 am, illini_99
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<""))

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