Conditional Ranking

M

matthew.clegg

I am trying to rank some data but with a condition.
Best seen by example:

Country Sales Value Rank Per Country
AUST. Dave 25 3
AUST. Bob 28 2
AUST. Alex 22 4
AUST. Phil 12 5
AUST. Rob 29 1
CHINA Janes 28 1
CHINA Phil 25 3
CHINA Dan 27 2
CHINA Alex 4 5
CHINA Bob 23 4

So I want to rank the value but per country, and the data is not in
order. In the real example there are a lot more countries. Ultimately
I would like to do this with multiple conditions, but happy to not
overcomplicate at this stage.

Any help much appreciated.
Thanks
Matt
 
B

Bernd

Or

=SUMPRODUCT(--(A$2:A$11=A2),--(C2<C$2:C$11))+SUMPRODUCT(--(A
$2:A2=A2),--(C2=C$2:C2))

if you need different ranks for identical values (first appearing get
higher ranks).

Regards,
Bernd
 
M

matthew.clegg

Or

=SUMPRODUCT(--(A$2:A$11=A2),--(C2<C$2:C$11))+SUMPRODUCT(--(A
$2:A2=A2),--(C2=C$2:C2))

if you need different ranks for identical values (first appearing get
higher ranks).

Regards,
Bernd

Ok both these are awesome. Would really appreciate it if you could
explain how they are working as it looks like very powerful
functionality.
Cheers
Matt
 
T

T. Valko

I'll explain the formula I suggested.

Let's use this smaller dataset:

...........A..........B..........C
1........X.........20..........2
2........X.........15..........3
3........X.........29..........1
4........Z.........50..........1
5........Z.........14..........2

The formula is counting how many entries meet the criteria and the result is
essentially a "rank".

=SUMPRODUCT(--(A$1:A$5=A1),--(B1<B$1:B$5))+1

Each of these expressions will return an array of either TRUE or FALSE:

(A$1:A$5=A1)
(B1<B$1:B$5)

A$1=A1 = T
A$2=A1 = T
A$3=A1 = T
A$4=A1 = F
A$5=A1 = F

B1<B$1 = F
B1<B$2 = F
B1<B$3 = T
B1<B$4 = T
B1<B$5 = F

The "--" coerces the TRUE or FALSE to 1 (TRUE) or 0 (FALSE)

--(A$1:A$5=A1)
--(B1<B$1:B$5)

A$1=A1 = T = 1
A$2=A1 = T = 1
A$3=A1 = T = 1
A$4=A1 = F = 0
A$5=A1 = F = 0

B1<B$1 = F = 0
B1<B$2 = F = 0
B1<B$3 = T = 1
B1<B$4 = T = 1
B1<B$5 = F = 0

The 2 arrays of 1's and 0's are then multipled together:

1 * 0 = 0
1 * 0 = 0
1 * 1 = 1
0 * 1 = 0
0 * 0 = 0

Then SUMPRODUCT adds up the results of that multiplication:

=SUMPRODUCT({0,0,1,0,0}) = 1

And the final step is to add 1:

=SUMPRODUCT(1) +1 = 2

So, B1 (20) is the 2nd largest value corresponding to "X".

You can do a reverse "rank" (lowest ranked higher) by simply changing the
"<" to ">" :

=SUMPRODUCT(--(A$1:A$5=A1),--(B1>B$1:B$5))+1

Biff
 
B

Bernd

Hi Biff,

That's a great explanation.

Matt, my second term just counts how many identical values appeared
from top up to the current one (similar to the first term which counts
how many [in total!] are smaller).

Is this sufficient for you?

Regards,
Bernd
 
M

matthew.clegg

Guys that's really great thanks.
Just posting another one now - hope you can help on this one too.
"Conditional Selection" - I think I could do it using a similar
formula to this one but there's an extra requirement...
 
M

matthew.clegg

Ok I thought I'd worked this all out but I tried to add another
condtion in as such:


=SUMPRODUCT(--(A$1:A$5=A1),,--(d$1:d$5<>0),--(B1>B$1:B$5))+1

where D would be filled with either 0,-1,1 and I want to limit my
ranking to those that have either 1 or -1 in D?
This still returns values for those with d values =zero?
 
T

T. Valko

Try it like this:

=IF(D1=0,"",SUMPRODUCT(--(A$1:A$5=A1),--(D$1:D$5<>0),--(B1>B$1:B$5))+1)

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Summing up Data 10
Vlookup function 1
Ranking Again 0
Need help on macro to do ranking 2
Ranking 2
How do I have excel highlight days before & after a date on a cale 2
Ranking 3
Not ranking 0's 2

Top