Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does -- do in Excel formulas?

Tags:

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.

like image 777
lucks Avatar asked Jul 20 '10 00:07

lucks


People also ask

What does '!' Mean in Excel formula?

When entered as the reference of a Named range , it refers to range on the sheet the named range is used on. For example, create a named range MyName refering to =SUM(!B1:!K1) Place a formula on Sheet1 =MyName . This will sum Sheet1! B1:K1.

What does =@ Mean in Excel?

The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1].

What does =+ in Excel do?

It has no meaning. The "+" after "=" is superfluous. It is a carryover from Lotus 1-2-3, where formulas can be entered as +123+456 instead of =123+456. Excel permits that form as well.


1 Answers

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.

like image 154
LittleBobbyTables - Au Revoir Avatar answered Oct 04 '22 21:10

LittleBobbyTables - Au Revoir