Establish ranks by group and points with a twist?

[freewaycb]

I am trying to establish group positions for competitors based on
penalty points and group. The catch being that equal times are
considered as a single placing as illustrated below. I'm used to
working what I will call normal ranking, using array formulas, to
produce a ranking as shown by the figures in brackets. What I'm trying,
and failing, to establish is a ranking where equal penalty points are
considered as a single placing as shown by the non bracket ranking
figures. I'd prefer to use worksheet formulas to calculate the rankng
as the points change although VBA to work the ranking after all the
points are entered would also be fine.

Comp Num. Points Group Rank within Group
1 10 1 1 (1)
2 12 1 2 (3)
3 10 2 1 (1)
4 12 1 2 (3)
5 13 2 2 (2)
6 12 1 2 (3)
7 13 2 2 (2)
8 14 2 3 (4)
9 10 1 1 (1)
10 15 1 3 (6)

Thanks
Chris Bruce

 

[Bernie Deitrick]

Chris,

You can use a User-Defined-Function.

Assuming your table is in A1:C11, then in cell D2, us the formula

=GroupRank(B2,$B$2:$B$11,$C$2:$C$11,C2,TRUE)

and copy down to D311.

Copy the code below, and paste into a code module in your workbook.

If you want the values in the parens, use this formula in cell D2, copied to
D311:

=SUMPRODUCT(($C$2:$C$11=C2)*($B$2:$B$11<B2))+1

HTH,
Bernie
MS Excel MVP

Function GroupRank(RankCell As Range, _
RankRange As Range, _
CritRange As Range, _
Criteria As Variant, _
Optional DescOrder As Boolean = True) As Integer

'=GroupRank(A1,$A$1:$A$10,$B$1:$B$10,B1, True)
'
'Where A1 has the number to be ranked,
'A1:A10 has the numbers against which A1 is to be ranked
'B1:B10 have the criteria
'"A" is the criteria (which can also be a Cell reference)
'True means smaller values get lower rank numbers, False would be the
opposite
'
'The function can be copied just like a regular function, and will return 0
'if the number to be ranked doesn't meet the criteria.

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myRange As Range
Dim myResult As Variant
On Error GoTo notRanked
For i = 1 To CritRange.Count
If CritRange(i) = Criteria Then
If myRange Is Nothing Then
Set myRange = RankRange(i)
Else
For j = 1 To myRange.Areas.Count
For k = 1 To myRange.Areas(j).Cells.Count
If myRange.Areas(j).Cells(k).Value = RankRange(i) Then
GoTo AlreadyThere:
End If
Next k
Next j
Set myRange = Union(myRange, RankRange(i))
AlreadyThere:
End If
End If
Next i
GroupRank = Application.Rank(RankCell.Value, myRange, DescOrder)
Exit Function
notRanked:
GroupRank = 0
End Function

 

[Domenic]

Here's another way...

Assuming that Column B contains the 'Points', and Column C contains the
'Group', try the following...

D2, copied down:

=SUM(IF(FREQUENCY(IF(($C$2:$C$11=C2)*(B2>$B$2:$B$11),$B$2:$B$11),IF(($C$2
:$C$11=C2)*(B2>$B$2:$B$11),$B$2:$B$11))>0,1))+1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!