Results 1 to 4 of 4

Thread: explanation of dashes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2004
    Location
    Lancashire
    Posts
    1,413
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default explanation of dashes

    Could anyone point me in the right direction or explain what the two dashes mean in some formulas, (an example below has been copied from a random post)
    =SUMPRODUCT(--($A$2:$A$10=G$2),--($B$2:$B$10=$F3),$C$2:$C$10)

    Thanks
    Colin.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,649
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    5 Thread(s)

    Default

    Have a look at the following...

    http://www.mcgimpsey.com/excel/formulae/doubleneg.html

    Hope this helps!

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,079
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: explanation of dashes

    Quote Originally Posted by rs2k
    Could anyone point me in the right direction or explain what the two dashes mean in some formulas, (an example below has been copied from a random post)
    =SUMPRODUCT(--($A$2:$A$10=G$2),--($B$2:$B$10=$F3),$C$2:$C$10)

    Thanks
    Colin.
    That "random post" wasn't that random...

    See:

    http://www.mrexcel.com/board2/viewtopic.php?t=73205

    At the risk of repeating what is already known, the syntax of SumProduct is:

    SUMPRODUCT(Array1,Array2,...)

    SumProduct multiplies the arrays it is given. The result of that multiplication is also an array, which SumProduct sums. Multiplication and summing say it all: Those arrays must all be numeric, that is, consist of numbers.

    If you use a CONDITIONAL like

    $A$2:$A$10=G$2

    instead of a numeric array, the result of the evaluation of the conditional will be an array/vector of truth values, something like:

    {TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

    an object SumProduct cannot do multiplication with. Fortunately, as we know, Excel also have numeric equivalents for the truth values of TRUE and FALSE.

    We also know something called COERCION, that is, changing from one data type to another. In case of the truth values, the following effects coercion from logical to numeric equivalents:

    =--TRUE ===> 1
    =TRUE+0 ===> 1
    =TRUE*1 ===> 1
    =TRUE^1
    =N(TRUE)

    If you substitute FALSE for TRUE above, youl'll get 0.

    From above:

    --($A$2:$A$10=G$2)

    will give:

    {1;0;0;0;1;0;0;0;1}

    0's help to eliminate irrelevant numbers in other arrays/vectors.

    Note that the so-called "array formulas" operate the same way.

    Edit for typo's.

  4. #4
    Board Regular
    Join Date
    Aug 2004
    Location
    Lancashire
    Posts
    1,413
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Many thanks for the replies,
    I will go and play with it for a bit now.

    Cheer's
    Colin.
    99% of PC problems occur at layer 8

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •