Forum Discussion
create rank top 10 and lowest 10 of students score ( no duplicate rank ) include 0 value,category
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 ".
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 ".
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
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!
Riny_van_Eekelen
indeed you are a truly master,i owe you this time.thanks once again to spare your time and humbly fix my excell issue,will have it fix and wont merge cell anymore... thanks a million!agusgusto Glad I could help.
