MrExcel.com - Your One Stop for Excel Tips & Solutions

Why the Minus Minus? Coerce Numbers from True/False

January 29, 2021 - by

Why the Minus Minus? Coerce Numbers from True/False

Challenge: While IF and other functions that expect logical tests can easily convert TRUE and FALSE values to 1s and 0s, the SUMPRODUCT function cannot do this. Why do you sometimes use a minus minus in SUMPRODUCT?

In Figure 9, for example, the SUMPRODUCT formula to calculate a 2% bonus for sales above $20,000 and with GP% above 50% fails:

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

If you simply build a SUMPRODUCT formula with your criteria and the numeric field, you end up with calculations such as TRUE * TRUE * 21000, which SUMPRODUCT incorrectly evaluates to 0.

Figure 9. You would think Excel’s Boolean logic rules could handle this.
Figure 9. You would think Excel’s Boolean logic rules could handle this.

In Figure 10, the first term of SUMPRODUCT has been evaluated. You see the array TRUE; TRUE, ....

Figure 10. The SUMPRODUCT function does not deal well with TRUE * TRUE * a number.
Figure 10. The 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.15), C4:C14)*0.02

Figure 11. By using minus minus, you convert the TRUE/FALSE to 1/0, and the formula works.
Figure 11. By using minus minus, you convert the TRUE/FALSE to 1/0, and the formula works.

Alternate Strategy: In fact, all the following operations also convert an array of TRUE/FALSE to an array of 1/0:

N(C4:C14>20000)
1*(C4:C14>20000)
(C4:C14>20000)+0
(C4:C14>20000)^0

You could multiply the criteria terms together, replace the comma with an asterisk, and let Excel perform all the logical tests. The formula to calculate the bonus would be:

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

This syntax allows you to combine AND and OR logic. Say that you want to pay the bonus if both conditions are met or if the rep is Joey. You would add some parentheses and indicate that the bonus is also paid when the rep is Joey:

=SUMPRODUCT(((C4:C14>20000) * (D4:D14>0.5)) + (B4:B14="Joey"), C4:C14)*0.02

Figure 12 shows a formula that conditionally sums based on two AND and one OR criteria.

Figure 12. You can build the Boolean logic as one term of the SUMPRODUCT function.
Figure 12. You can build the Boolean logic as one term of the SUMPRODUCT function.

Summary: To use logical tests in SUMPRODUCT, you can convert the TRUE/ FALSE values to 1 / 0 values by using minus minus or other methods described in this topic.

Source: "Up all night.. this better not be an easy answer!" and "explanation of dashes" on the MrExcel Message Board.

Title Photo: Nicolas Hoizey at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.