Exclude cell from Rank Array

Thanks:  0
Likes:  0

# Thread: Exclude cell from Rank Array

1. ## 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?

2. ## Re: Exclude cell from Rank Array

Care to post the desired results?

Sure.

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

4. ## Re: Exclude cell from Rank Array

Originally Posted by KyleG
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)

5. ## Re: Exclude cell from Rank Array

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. ## Re: Exclude cell from Rank Array

Originally Posted by Matty

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.

7. ## 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. ## Re: Exclude cell from Rank Array

Originally Posted by Matty
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...

9. ## Re: Exclude cell from Rank Array

WAYYYYY over my head fellas but thanks anyway.

Have a good one.

10. ## 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)

## User Tag List

#### Posting Permissions

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