# Why two minus signs "--" in an excel formula?

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.

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.

Following the values in cell A1:B8

 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)

#### Excel Tutorials : 100 Excel Tutorials

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn