Using two minus signs next to each other causes the formula to convert a return value of “TRUE” into 1 and a return value of “FALSE” into 0.
Let’s start with a simple logical condition :3 > 2 .
Apply this in excel = 3>2 returns “TRUE” . Using “—“ two minus signs together with this condition returns 1 = (3>2)
This method is mostly used in SUMPRODUCT formulas to convert the conditional arrays that evaluates to TRUE/FALSE into 1/0.
Note : SUMPRODUCT formula ignores non numeric cells. Using double minus signs technique we can command excel to convert TRUE/FALSE to 1/0.
A  B  
1 
X

Y 
2 
20

2 
3 
10

5 
4 
5

8 
5 
15

11 
6 
7

14 
7 
8

17 
8 
13

20 
Use this formula =SUMPRODUCT((A2:A8<12),B2:B8)
The first minus sign coerces the array to (0,1,1,0,1,1,0) and the second minus sign multiplies each values with negative sign [ Basic Maths : () * () = + ] (0,1,1,0,1,1,0)
So the sumproduct evaluates =SUMPRODUCT((0,1,1,0,1,1,0),(2,5,8,11,14,17,20)) returns a value 44.
You can figure out the same thing using the below formula :
=SUMPRODUCT((A2:A8>10)*B2:B8)
Superb. Thanks for sharing your knowledge.
ReplyDeleteCan you please also add one of the most power full excel tool "Turning Data into Decisions" i.e. PivotTable, PowerPivot and PivotCharts?