Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions |