Forum Discussion

agusgusto's avatar
agusgusto
Copper Contributor
Jan 25, 2021
Solved

create rank top 10 and lowest 10 of students score ( no duplicate rank ) include 0 value,category

hi i am currently trying to create a auto sorting data of rank out of a list of student's score list. in a separate worksheet ,whenever i would like see rank either top 10 or lowest 10 of certain ca...
BI & Data Analysis
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 25, 2021

    agusgustoPlease see if the formula in the attached workbook work for you.

    Top scores by location:

    =SORTBY(FILTER(data!C5:C11,data!B5:B11=$C$5,"-"),FILTER(data!AI5:AI11,data!B5:B11=$C$5,"-"),-1)

     

    Bottom scores by location

    =SORTBY(FILTER(data!C5:C11,data!B5:B11=$C$5,"-"),FILTER(data!AI5:AI11,data!B5:B11=$C$5,"-"),1)

     

    Rank per class:

    =SORTBY(FILTER(data!C5:C11,data!A5:A11=$C$5,"-"),FILTER(data!AI5:AI11,data!A5:A11=$C$5,"-"),-1)

    I would recommend that, in your real schedules, you use structured tables and/or dynamic named ranges. This will avoid you from having to update the cell references every time the data ranges change. 

    Furthermore, consider getting rid of merged cells. You seem to use them for cosmetic reasons only.  And make sure that the location names and class codes are spelled exactly the same in both the data and the ranking tables. I found both locations "sumatra" and "sumatra " (i.e. with a space at the end). Similarly for classes, I found "C" and "C ".

agusgusto's avatar
agusgusto
Copper Contributor
Jan 27, 2021

Riny_van_Eekelen 

 

i found out in mya 2007 excell i couldnt find  "SORTBY  OR FILTER " function is there any other way to find the same result in older version of excell ?

 

Cheers

 

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 27, 2021

agusgusto Yes! Possible, but the formulae would be quite complicated and not very user friendly and therefore difficult to maintain and adapt to your real situation, which probably isn't as simple as the example file you provided. I favour a more straight-forward approach that should also work in E2007. Pivot tables are great to do exactly what you want, and they are not very difficult to learn.  I've added a few pivot tables to your file (attached below) to demonstrate their use. Give it a try!

Resources