Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
Try this:
=SUMPRODUCT(--(A$2:A$11=A2),--(C2<C$2:C$11))+1 Copied down Biff wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
On Jun 1, 7:27 pm, Bernd wrote:
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
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),--(B1B$1:B$5))+1 Biff wrote in message oups.com... On Jun 1, 7:27 pm, Bernd wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
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... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
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),--(B1B$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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Ranking
Try it like this:
=IF(D1=0,"",SUMPRODUCT(--(A$1:A$5=A1),--(D$1:D$5<0),--(B1B$1:B$5))+1) Biff wrote in message ups.com... 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),--(B1B$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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking | Excel Discussion (Misc queries) | |||
Ranking | Excel Worksheet Functions | |||
ranking | Excel Worksheet Functions | |||
ranking | Excel Worksheet Functions | |||
Ranking | Excel Discussion (Misc queries) |