Rank & Sort Table

    I have a list in rows where I have a ranking formula


    =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+COUNTIF($G$5:G5,G5)-1)+1


    When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending


    ex) 1.75 1
    2.5 2
    3.75 3
    4 18
    4 17
    4 16


    how do I sort the entire list in ascending order?

    Re: Ranking And Sorting


    It's your formula that forces the apparent sort order of non-unique items.


    Try changing the formula to:
    =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+COUNTIF(G5:$G$81,G5)-1)+1


    BTW, I used the formula below in my testing and it worked and have tried to derive the formula above from it to match your ranges.. there may be a mistake, so I include the working formula below.
    =COUNT($A$1:$A$6)-(RANK(A1,$A$1:$A$6)+ COUNTIF(A1:$A$6,A1)-1)+1


    p45cal

    Re: Ranking And Sorting


    Welcome to Oz, suncluster :)


    What does the formula accomplish that sorting the raw range does not?

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Sort With Formulas


    I noticed that too. The OP's formula appears to turn equal rankings into a sequential ranking. Sorting the raw range still results in not being able to see the non-unique items' rankings in ascending order (using the OP's original formula). My guess is that the OP did sort on the raw range first, but noticed the out-of-order rankings, so went on to sort on the rankings themselves and was disappointed to see that they were still in the wrong order!
    p45cal

    Re: Sort With Formulas


    Is there a better way to carry out this task?


    My intention using this formula was not to sort the data but to rank it.


    My data:
    a b c d e f g h
    Bob Ted Alice Jay Fran Jack Average "Dept Priority"


    every line item (1,2,3,...) refers to a specific project
    Columns A-F list the comittee members personal ranking of the project
    Column G is there average rank using the formula below
    =AVERAGE(A6,B6,C6,D6,E6,F6)
    Column H is meant to rank based on the averages
    =COUNT($G$6:$G$80)-(RANK(G6,$G$6:$G$80)+ COUNTIF(G6:$G$6,G80)-1)+1



    Then, I sort the data in ascending order.

    Re: Rank & Sort Table


    Right, now I'm beginning to see what you're trying to do. A low average is a high priority, and RANK as you've used it ranks higher numbers with lower numbers, so in the formula you add a COUNT statement of the whole range then subtract the rank from it. I hope so. [I didn't examine your formula closely the first time (out of laziness). I just adjusted it to give what I thought you wanted (and still think you want).]
    Did you know there is a third argument of the RANK function; if it isn't 0 (the default if omitted) it does all this for you.


    [SIZE="1"]In the following formulae I use G6:G14 instead of your G6:G80 because I wasn't going to create that much data for testing.[/SIZE]


    So in H6:
    =RANK(G6,$G$6:$G$14,1)
    copied down to H14, gives the lowest average a rank of 1, but where avarages are equal it gives the same ranking (of course).


    That might be all you want, but the COUNTIF part of your formula says 'no'. I'm guessing that after sorting on column G, instead of seeing a ranking like:
    1,2,3,4,5,5,5,8,9
    you want to see:
    1,2,3,4,5,6,7,8,9
    in which case in H6:
    =RANK(G6,$G$6:$G$14,1)+COUNTIF(G$6:$G6,G6)-1
    copied down does this and incidentally, gives the same result as my adaptation suggested in message #2 above. Realise that by doing this, you are artificially ranking some projects lower than others when they originally had equal ranking, and that the ones that get the higher rankings is not under any specific control.


    Finally, sort ascending on column G.


    I really hope this answers your question,
    p45cal

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login