Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Exclude cell from Rank Array

This is a discussion on Exclude cell from Rank Array within the Excel Questions forums, part of the Question Forums category; i want to (as the title says) exclude cells from a rank array if the cell in the adjacent cell ...

  1. #1
    Board Regular
    Join Date
    Jun 2004
    Location
    New Zealand
    Posts
    617

    Default Exclude cell from Rank Array

    i want to (as the title says) exclude cells from a rank array if the cell in the adjacent cell is 0.

    75 4
    40 4
    90 4
    86 4
    32 4
    100 4
    100 0
    88 4

    In this example it would not include the 100 with the 0 in the array.
    is this possible?
    Kyle

    "Bad is never Good until Worse happens."

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,788

    Default Re: Exclude cell from Rank Array

    Care to post the desired results?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Jun 2004
    Location
    New Zealand
    Posts
    617

    Default Re: Exclude cell from Rank Array

    Sure.

    75 4 5
    40 4 6
    90 4 2
    86 4 4
    32 4 7
    100 4 1
    100 0 -
    88 4 3
    Kyle

    "Bad is never Good until Worse happens."

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,788

    Default Re: Exclude cell from Rank Array

    Quote Originally Posted by KyleG View Post
    Sure.

    75 4 5
    40 4 6
    90 4 2
    86 4 4
    32 4 7
    100 4 1
    100 0 -
    88 4 3
    Try...

    C2, copied down:

    =IF(B2=0,"",SUMPRODUCT(1-($B$2:$B$9=0),--(A2 < $A$2:$A$9))+1)
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,244

    Default Re: Exclude cell from Rank Array

    Hello Aladin,

    I've run the formula you posted through Excel's evaluator, but I don't quite follow how it's evaluating correctly.

    Could you explain the logic of it, please?

    Thanks,

    Matty

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,788

    Default Re: Exclude cell from Rank Array

    Quote Originally Posted by Matty View Post
    Hello Aladin,

    I've run the formula you posted through Excel's evaluator, but I don't quite follow how it's evaluating correctly.

    Could you explain the logic of it, please?

    Thanks,

    Matty
    A2 < $A$2:$A$9 does an ascending order evaluation, that is, it yields a count for A2's being smaller than the numbers it's compared with. This evaluation is done only when corresonding values are not zero.

    We get successively:

    SUMPRODUCT({1;1;1;1;1;1;0;1},{0;0;1;1;0;1;1;1})+1 [for A2]

    The first array/vector {1;1;1;1;1;1;0;1} is the result of:

    1-{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

    where 1-FALSE = 1 and 1-TRUE = 0.

    The second vector {0;0;1;1;0;1;1;1} is the result of:

    --(75 < {75;40;90;86;32;100;100;88})

    --({FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE})

    where --FALSE = 0 and --TRUE = 1.

    The multiplication of

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

    with

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

    yields:

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

    the sum of which is 4. Finally, 1 is added to this result in order to obtain the rank value of 5 for the number in A2.

    Hope this helps.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,244

    Default Re: Exclude cell from Rank Array

    Thanks for the detailed explanation, Aladin. It's much appreciated.

    Now all I have to do is remember the trick of using 1- to get the direct opposite of double unary coercion!

    Thanks again.

    Matty

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,788

    Default Re: Exclude cell from Rank Array

    Quote Originally Posted by Matty View Post
    Thanks for the detailed explanation, Aladin. It's much appreciated.

    Now all I have to do is remember the trick of using 1- to get the direct opposite of double unary coercion!

    Thanks again.

    Matty
    You are welcome.

    Mathematical operators effect, by the way, all coercion from logical to number and from text number to true number...
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Jun 2004
    Location
    New Zealand
    Posts
    617

    Default Re: Exclude cell from Rank Array

    WAYYYYY over my head fellas but thanks anyway.

    Have a good one.
    Kyle

    "Bad is never Good until Worse happens."

  10. #10
    New Member
    Join Date
    Jun 2011
    Posts
    18

    Default Re: Exclude cell from Rank Array

    Hi all,
    Rather than starting up a new thread on a similar topic, I thought I could piggyback this one. I (think) I am trying to do what the OP was attempting, but please correct me if I am wrong.

    I am hoping to exclude a cell from ranking in the array I have used, supplied from this thread. My parameters for excluding said cells are if the value of another cell is less than a set value. Like so:

    300 85% 3
    280 86% 2
    300 72% 5
    420 79% 4
    430 93% 1
    40 90% -
    25 88% -

    Column A = if this value is less than 100, it does not get ranked.
    Column B = value I am hoping to rank based on the (rankif, sumproduct, if statement best to use)
    Column C = target output results (ranking)

    Thank you all in advance!

Page 1 of 4 123 ... LastLast

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
  •  


DMCA.com