What — does in Excel?

Question

Trying to decipher some Excel formulas and I see some stuff like SUMPRODUCT(--Left(...)...)

What is the -- doing? Naturally seems like decrementing to me but couldn't find any documentation on it.

Thanks.

Answer 1

The double-dash is known as a double unary operator.

Try this link: Why use -- in SUMPRODUCT formulae

Specifically:

SUMPRODUCT() ignores non-numeric entries. A comparison returns a boolean (TRUE/FALSE) value, which is non-numeric. XL automatically coerces boolean values to numeric values (1/0, respectively) in arithmetic operations (e.g., TRUE + 0 = 1).

The most efficient way to coerce the value is first to apply the unary minus operator, coercing TRUE/FALSE to -1/0, then applying it again to negate the value, e.g., +1/0.

A single unary operator (-) coerces true/false values into -1/0. By using the double unary operaor, we coerce the values again to 1/0.

Answer 2

I've been using SUMPRODUCT for a while and have always used the * symbol instead of the --. I'm sure I asked the same question you've asked, but I can't remember the reason they gave me, but I was told that there wasn't really a need for --as sumproduct managed itself quite well without the it.

Anyway, =sumproduct(()*()*()*()) has always worked for me, and it's less confusing.

Answer 3

Boolean values TRUE and FALSE in excel are treated as 1 and 0, but we need to convert them. To convert them into numbers 1 or 0, do some mathematical operation. The Unary operator negates the boolean (math operation), hence, converts the boolean to number. Same works in TRUE * FALSE = 0

Answer 4

The unary operator (-) is a shorthand method to convert a true/false statement into -1/0.

A single operator will convert -(true) into -1, so a double unary operator is used to convert that back into 1:

-(-(true)) = -(-(1)) = 1
-(-(false)) = -(-(0)) = 0