Formual Syntax?

[QTE]

Hi Excel Forum,

What do the two dashes (--) mean and do in the following formulas:


=SUMPRODUCT(--A1:G1)

=SUM(IF(ISNUMBER(--A1:G1),--A1:G1))
entered with ctrl + shift & enter

=SUMPRODUCT(--(ISNUMBER(A1:G1)))

Please explain.

Kind regards,
QTE

 

[Norman Harker]

Hi QTE!

The -- coerces Boolean returns of TRUE and FALSE to 1 and 0

Do a Google Search on "unary minus" and you'll find plenty of
discussions on the topic.

 

[JE McGimpsey]

Not the Excel Forum (rather the micorosoft.public.excel.newusers
newsgroup), but...

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.

The array is then numeric and will be evaluated by SUMPRODUCT.

 

[QTE]

Hi JE McGimpsey,

Thank you very much. Great help.

Kind regards,
QT