Conditional Ranking

[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

 

[T. Valko]

Try this:

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

Copied down

Biff

 

[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

 

[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. 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

 

[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

 

[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...

 

[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. Valko]

Try it like this:

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

Biff