Dashes in Functions
 

Has anyone ever seen the use of "dashes" in functions or formulas before? I
have a user who has several instances where dashes have been used (someone
sent her the spreadsheet) and none of us have ever seen this before. An
example follows:

=SUMPRODUCT(--(F3:F838<121),--(E3:E838=1))

Any suggestions / explanations?
Thanks,
--
LPS

Dashes in Functions
 

They're just minus signs. By applying a unary minus operation to a
true/false, you force Excel to turn the booleans into numbers. By applying
the minus twice, you get back to a positive number.
So --(true) is 1; --(false) is 0.

"LPS" wrote:

Has anyone ever seen the use of "dashes" in functions or formulas before? I
have a user who has several instances where dashes have been used (someone
sent her the spreadsheet) and none of us have ever seen this before. An
example follows:

=SUMPRODUCT(--(F3:F838<121),--(E3:E838=1))

Any suggestions / explanations?
Thanks,
--
LPS

Dashes in Functions
 

This expression:
F3:F838<121
expands into 836 true/falses (one for each cell in F3:F838)

The first minus changes true to -1 (and false to 0). The second minus changes
-1 to 1 (and 0 to 0).

=sumproduct() likes to work with numbers, so this is a quick way to change those
boolean values to numbers.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



LPS wrote:

Has anyone ever seen the use of "dashes" in functions or formulas before? I
have a user who has several instances where dashes have been used (someone
sent her the spreadsheet) and none of us have ever seen this before. An
example follows:

=SUMPRODUCT(--(F3:F838<121),--(E3:E838=1))

Any suggestions / explanations?
Thanks,
--
LPS

--

Dave Peterson