Microsoft Office Tutorials and References

In Depth Information

**WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE**

In Figure 10, the ﬁ rst term of
SUMPRODUCT
has been evaluated. You see the

array
TRUE;TRUE,
….

Part

I

Figure 10.
h e
SUMPRODUCT
function does not deal well with

TRUE * TRUE *
a number.

Solution:
You need a way to convert the
TRUE/FALSE
values to
1/0
values.

Excel gurus use the minus minus in order to coerce Excel to change an array

of
TRUE/FALSE
values to
1s
and
0s
:

--(C4:C14>20000)

As shown in Figure 11, this formula does the trick:

=SUMPRODUCT(--(C4:C14>20000),--(D4:D14>0.5),C4:C14)*0.02

Figure 11.
By using minus

minus, you convert the

TRUE/FALSE
to
1/0
,

and the formula works.