Exclude cell from Rank Array

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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:

<table style="border-collapse: collapse; width: 144pt;" border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" align="right" height="17" width="64">300</td> <td style="width: 48pt;" align="right" width="64">85%</td> <td style="width: 48pt;" align="right" width="64">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">280</td> <td align="right">86%</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">300</td> <td align="right">72%</td> <td align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">420</td> <td align="right">79%</td> <td align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">430</td> <td align="right">93%</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">40</td> <td align="right">90%</td> <td class="xl65"> -</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">25</td> <td align="right">88%</td> <td class="xl65"> -</td> </tr> </tbody></table>
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!
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top