Forum Discussion
Create a Rank based on the Frame Played and Won
I have been trying to create a Rank based on the Frame played and Won. All i got is the Rank formula from the Google.
=RANK(D2,$D$2:$D$18)
But i want a fair Ranks if player has played 1 frame and won 1 then its ranks will be next from the player who played 3 frames and won 3.
I have attached a picture with Ranks Result and The Result I want.
Your help will be appreciated.
The following assumes that you always sort the results descending on % and Frames Won, as in your sample workbook.
In E2, enter 1 (the top entry will always have rank 1 because of the sort order).
In E3, enter the formula =E2+OR(D3<>D2,B3<>B2)
This adds 1 to the rank if either % or Frames Won changes.
Fill or copy down to the last row with data.
7 Replies
The following assumes that you always sort the results descending on % and Frames Won, as in your sample workbook.
In E2, enter 1 (the top entry will always have rank 1 because of the sort order).
In E3, enter the formula =E2+OR(D3<>D2,B3<>B2)
This adds 1 to the rank if either % or Frames Won changes.
Fill or copy down to the last row with data.
- thank you its working perfect. Sir one more favor please if i want to do it via VBA can you please do this
For example:
Sub SortAndRank() Dim LastRow As Long ' Speed up execution Application.ScreenUpdating = False ' Find the last row LastRow = Range("A" & Rows.Count).End(xlUp).Row ' Sort descending on % and Framse Played Range("A1:E" & LastRow).Sort _ Key1:=Range("D1"), Order1:=xlDescending, _ Key2:=Range("B1"), Order2:=xlDescending, _ Header:=xlYes With Range("E2:E" & LastRow) ' Add formulas .Formula = "=SUM(E1,OR(D2<>D1,B2<>B1))" ' Optional: replace formulas with their result .Value = .Value End With ' Show the result Application.ScreenUpdating = True End Sub
