The idea of using two minus signs together in an Excel formula might seem odd to many. However, doing so offers some rather interesting and powerful options. In this tip, we will describe and demonstrate how and when you should use two minus signs in tandem when building Excel formulas.
As everyone knows, a single minus sign in a formula causes Excel to change the sign of the result – for example, from positive to negative. But what do two minus signs in a formula do? Using two minus signs in an Excel formula causes the formula to transform a result of “True” into a value of “1” and a result of “False” into a value of “0.” Using two minus signs in a formula has no effect on any result other than “True” or “False.”
In the very simple examples shown in Figure 1, cell C3 contains the formula shown in cell B3, =(5>3), and because that condition is true, the result of the formula is “TRUE,” as pictured in cell C3. However, the formula in cell C4 is that shown in cell B4, =--(5>3). Though the condition is still true, the result of the formula appearing in cell C4 is “1,” because of the presence of the double minus signs.
Figure 1 - Sample Formula With Double Minus Signs
Perhaps the most common application of using this technique is found when using Excel’s SUMPRODUCT function to sum data that meets multiple criteria. For instance, in the worksheet pictured in Figure 2, we could use a SUMPRODUCT formula with double minus signs to sum all of the data where the Region is “Midwest” and the Product Line is “Masks.”
Figure 2 - Sample Data For SUMPRODUCT
The syntax of that formula would be as shown below.
This formula works by creating an array of data that is four columns wide. The first two columns consist of only 0’s and 1’s and these values are generated by the results of the
--(B7:B30="Midwest") and --(C7:C30="Masks") statements. The third column in the array is simply the amounts found in cells D7:D30. The fourth column of the array is the product of multiplying the first three columns together. Pictorially, this is represented in Figure 3. Note that the array shown in Figure 3 is presented for instructional guidance only; the SUMPRODUCT does not create such an array on the face of the worksheet.
Figure 3 - Array Effectively Created By SUMPRODUCT With Double Minus Signs
Anytime you need to use some type of logical function in Excel and wish to have the results returned as either “1” or “0” instead of “TRUE” or “FALSE,” simply use double minus signs as part of your formula and you will be well on your way to creating some powerful Excel formulas.
To view a video demonstration of this tip, please visit www.tinyurl.com/k2tips103.