Forum Discussion
Ranking Help needed for unsorted data
Hi I have data similar to what is in Colums A, B & C. My objective is to get data(Ranking) in column D as seen in the image. Can you guys please help me. Basically what i want to get is Ranking of ...
Yeah I got what you did here but i want it on the data set so that I can then apply a Pivot on the base data to show only the Top 2/3 ranked Subject for each student.
If I do it as per your method, I will not be able to apply a universal Top2/3 rank filter.
Apologies I didn't explain properly earlier what is the end result I wanted & thanks for the effort to reply to my message
If I do it as per your method, I will not be able to apply a universal Top2/3 rank filter.
Apologies I didn't explain properly earlier what is the end result I wanted & thanks for the effort to reply to my message
If you format your range https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370 you won't have to copy down + it's highly recommended if you need a Dense Rank. The formula is column F performs "intensive" array calcs. so limiting the arrays to the actual used "range" is quite important
Assuming above Table1
in E3:
=COUNTIFS([Name],[@Name], [Marks],">"&[@Marks])+1in F3:
=SUM(
--(
FREQUENCY(
([Name]=[@Name])*([Rank]<[@Rank])*[Rank],
([Name]=[@Name])*([Rank]<[@Rank])*[Rank]
) > 0
)
)