Care to post the desired results?
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 ...
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."
Care to post the desired results?
Assuming too much and qualifying too much are two faces of the same problem.
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."
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.
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
WAYYYYY over my head fellas but thanks anyway.
Have a good one.
Kyle
"Bad is never Good until Worse happens."
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!
Like this thread? Share it with others