Ranking with IF statement

[imgonna]

Ok folks -


I have two columns, BAND and SCORE


I want to rank SCORE, BUT BAND has two options (x) or (y). So I want a ranking of SCORE if BAND = x and if BAND = y


eg


Band Sore

x 120 (rank = 2 of the "x")

x 111 (rank = 3 of x)

y 101 (rank = 3 of y)

x 129 rank = 1 of x

y 120 rank = 2 of y

y 120 rank = 2 of y


Am OK with the RANK, just cant figure out how to split by X and Y.


Any pointers?


Cheers

G

 

[imgonna]

So, I found this (which works)


=MATCH(B3,LARGE(IF($A$3:$A$11=A3,$B$3:$B$11),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$11,A3)))),0)


Which does exactly what it is supposed to.


Great.


I read somewhere about adding a random number to a list to force a ranking -- but when I do that, the random number recalcs everytime, so the rank changes.


NEarly there...


G

 

[Luke M]

Unfortunately, the RANK function is setup so that it must take a "reference" as an argument, as opposed to an array (seen in the LARGE/SMALL functions). This limits what we can do because we can't use a logic filter to pass an array to the RANK function. However, we can work around this usually by realizing that RANK works by calculating how many values within the array are greater/less than the specific number. This formula then becomes:


=SUMPRODUCT(((($A$2:$A$7=A2)*($B$2:$B$7))>B2)*1)+1


The ">B2" indicates that higher values will be ranked 1. Change this to a < if you want smaller values to be preferred. Also, in your example, the 2 values of 120 for y should have a rank of 1, as both have tied for 1st place (similar to Olympics).

 

[imgonna]

Hi Luke


Perfect, and I stand more chance of understanding it due to the copious SUMPRODUCT examples out there.


Yea, the shared ranking is OK - what I am trying to do is to cream off the top n numbers and allocate them to "set 1", the next y numbers to "set 2" and so on.


Ranking them is a start.


OR I go down the VLOOKUP route -- but the values I'm looking up dont matter. I just want the TOP 10, next 10 etc etc.


I suppose I could "normalise" the data by dividing the Score by the Max value so that it is always a number from 0 to 1...... (<<< might be my preferred solution).


Thanks again for the formula.


I think someone should "curate" and Excel "formula" vs "scenario" list ....... ;-)


Cheers

G

 

[imgonna]

Hi


I think I'll go down this route:


={N12/MAX(IF(Bandxy=L12,Score))}


To compute the "normalised" score and use VLOOKUP to pull a set number....


G

 

[Luke M]

Interesting solution, thanks for sharing with us. I admit, I don't quite understand what it is you're looking for, but I'm sure that with all the data in front of you, it does! =P

 

[imgonna]

Solution:


Thanks to Luke for your input - you are most awesome..


1) Calculate unique score:


=IF(ISERR(AVERAGE(P12:U12)),"",(AVERAGE(P12:U12)+(ROW()/1000)))


Bascially compute the average score and add a tiny bit depending on the row that the data is in. Always unique.


Then used Luke's formula to rank(if):


=SUMPRODUCT((((Bandxy=L12)*(Score))>N12)*1)+1


Elegant and did not need an intermediate column.


Cheers


G

 

[imgonna]

To answer Luke's question:


I am taking a list of student scores and automatically allocating them to a group based on their average test score.


The whole student body is either "X" or "Y" - hence the need to rank(if).


My initial solution was just a fudge to scale all the numbers so I could use VLOOKUP to place them in a set.


Now I have a real Ranking, I can just allocate 1-30 in class 1, 31-60 in class 2 etc etc.


(this is proof of concept and not how we really do it -- we sit with coffee and endlessly debate which student goes in which class).


Karma Luke


x

 

[NARAYANK991]

Hi All ,


Just for the record , in case anyone wants more explanations :


1. http://whatapalaver.co.uk/2009/09/conditional-ranking-in-excel-rankif-using-sumproduct/


2. http://www.youtube.com/watch?v=7S0eDH2j-qk


3. http://www.get-digital-help.com/2009/04/25/how-to-rank-text-uniquely-in-excel/


4. http://www.get-digital-help.com/2009/04/26/how-to-rank-uniquely-with-criteria-in-excel-2007/


Narayan