Menu
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.
777
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.
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].
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.
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.
154
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
