Microsoft Office Tutorials and References
In Depth Information
WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE
In Figure 10, the fi 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.
 
Search JabSto ::




Custom Search