Top ten ordering in Excel based on complex team rules

Question

I have an excel spreadsheet in a format similar to the following...

| NAME  | CLUB | STATUS | SCORE |
| Fred  |  a   | Gent   | 145   |
| Bert  |  a   | Gent   | 150   |
| Harry |  a   | Gent   | 195   |
| Jim   |  a   | Gent   | 150   |
| Clare |  a   | Lady   | 99    |
| Simon |  a   | Junior | 130   |
| John  |  b   | Junior | 130   |
   :
   :
| Henry |  z   | Gent   | 200   |

I need to convert this table into a list of the "Top Ten" teams. The rules are

• Each team score is taken from the sum of four members of that club.
• These totals should be of the best four scores except...
  • Each team must consist of at least one Junior or Lady

For example in the table above the team score for club A would be 625 not 640 as you would take the scores for Harry(190), Bert(150), Jim(150), and Simon(130). You could not take Fred's(145) score as that would give you only Gents.

My question is, can this be done easily as a series of Excel formula, or will I need to resort to using something more procedural?

Ideally the solution needs to be automatic in the team selections, I don't want to have to create separate hand crafted formula for each team. I also will not necessarily have a neatly ordered list of each clubs members. Although I could probably generate the list via an extra calculation sheet.

Thanks

Peter

Answer 1

Use a pivot table which will act as a database query on the data you have. Pivot so that the teams go down the columns and team members along with their status type go across the pivot table. I'm not sure for 2003, but Excel 2007 lets you then sort so the highest scores appear to the left. Then your first sum can simply take the first three scores for the each team. However to get the last persons sum, you have to determine if you can use the 4th score, or if you have to use the max of the junior or Lady types. That could be done using a complex and brute force formula somewhat like this:

if (type of position 1 is a junior or a lady or ... 2 or 3... ) then use position 4 else if position 5 is a junior or lady then use 5 else if p 6 is ... and so on.

Answer 2

Writing a solution in VBA would be my first choice, especially if the rules have the possibility of becoming more complex.

Answer 3

I don't think that this can be done unless the table is sorted in some way. Most of Excel's lookup functions require ordered lists. This could certainly be done with a VBA function.

Answer 4

Public Function TopTen(Club As String, Scores As Range)

Dim i As Long
Dim vaScores As Variant
Dim bLady As Boolean
Dim lCnt As Long
Dim lTotal As Long

vaScores = FilterOnClub(Scores.Value, Club)
vaScores = SortOnScore(vaScores)

For i = LBound(vaScores, 2) To UBound(vaScores, 2)
    If lCnt = 3 And Not bLady Then
        If vaScores(3, i) <> "Gent" Then
            lTotal = lTotal + vaScores(4, i)
            bLady = True
            lCnt = lCnt + 1
        End If
    Else
        lTotal = lTotal + vaScores(4, i)
        lCnt = lCnt + 1
        If vaScores(3, i) <> "Gent" Then bLady = True
    End If

    If lCnt = 4 Then Exit For
Next i

TopTen = lTotal

End Function

Private Function FilterOnClub(vaScores As Variant, sClub As String) As Variant

Dim i As Long, j As Long
Dim aTemp() As Variant

For i = LBound(vaScores, 1) To UBound(vaScores, 1)
    If vaScores(i, 2) = sClub Then
        j = j + 1
        ReDim Preserve aTemp(1 To 4, 1 To j)
        aTemp(1, j) = vaScores(i, 1)
        aTemp(2, j) = vaScores(i, 2)
        aTemp(3, j) = vaScores(i, 3)
        aTemp(4, j) = vaScores(i, 4)
    End If
Next i

FilterOnClub = aTemp

End Function

Private Function SortOnScore(vaScores As Variant) As Variant

Dim i As Long, j As Long, k As Long
Dim aTemp(1 To 4) As Variant

For i = 1 To UBound(vaScores, 2) - 1
    For j = i To UBound(vaScores, 2)
        If vaScores(4, i) < vaScores(4, j) Then
            For k = 1 To 4
                aTemp(k) = vaScores(k, j)
                vaScores(k, j) = vaScores(k, i)
                vaScores(k, i) = aTemp(k)
            Next k
        End If
    Next j
Next i

SortOnScore = vaScores

End Function

Use as =TopTen(H2,$B$2:$E$30) where H2 contains the club letter.

Answer 5

can this be done easily as a series of Excel formula

Short answer, YES. (Depending on your definition of "easily").

Long answer...

(I think this works)

Here's my (brief) test data:

    A          B    C        D
 1 NAME      CLUB STATUS  SCORE
 2 Kevin    a Gent    145
 3 Lyle     a Gent    150
 4 Martin   a Gent    195
 5 Norm     a Gent    150
 6 Oonagh   a Lady    100
 7 Arthur   b Gent    200
 8 Brian    b Gent    210
 9 Charlie  b Gent    190
10 Donald   b Gent    220
11 Eddie    b Junior  150
12 Quentin  c Gent    145
13 Ryan     c Gent    150
14 Sheila   c Lady    195
15 Trevor   c Gent    150
16 Ursula   c Junior  200

Now, if I've understood the rules correctly, we want the best four scores, except that if the highest score by either a lady or a junior is not in the best four, we use that instead of the fourth highest. I've restated it somewhat, for reasons that may become apparent...

OK. Array formulae to the rescue! (I hope)

The highest score from team a should be

{=LARGE(IF(B2:B16="a",D2:D16,0),1)}

where the {} indicates an array formula created by using Control-Shift-Enter to input the formula. The top four are similarly created. For the Lady/Junior bit, we need a bit more complexity. Taking the Lady, we need this:

{=LARGE(IF($B$2:$B$16=$J3,IF($C$2:$C$16="Lady",$D$2:$D$16,0),0),1)}

Junior may safely be left as an exercise for the student, I hope.

I'm now looking at a table with the following layout for club "a"

     J    K      L      M      N      O      P
 1 Club    1      2      3      4   Lady  Junior
 2 a     195    150    150    145    100      0

The club score should be the top three "anyone" scores plus the best lady or junior if they're not already in the top four.

So in Q2 I'm putting this:

=SUM(K2:M2)+MIN(MAX(O2,P2),N2)

MAX(O2,P2) tells me the best lady or junior score, which has to be included. If it's higher than the fourth-highest team score, then it's already in the list and we just take the top four. Otherwise, we replace the fourth-highest score with the best lady/junior one.

Now we could do it all in one formula, by substituting the parts into the final formula:

{=LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),1)+
LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),2)+
LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),3)+
MIN(LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),4),
MAX(LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Lady",$D$2:$D$18,0),0),1),
LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Junior",$D$2:$D$18,0),0),1)))}

But I don't recommend it...

So for the above data, I end up with this:

            Anyone                                      Lady       Junior               
Club        1          2          3          4          1          1          Total     
a           195        150        150        145        100        0          595       
b           220        210        200        190        0          150        780       
c           200        195        150        150        195        200        695       

Rats. In my excitement at (I think) getting the hard part to work I forgot to mention that

• The list of scores can be in any order
• You can get the club rankings with RANK()
• You can then pull the top 10 into another table using MATCH() and INDEX()

    A               B     C     D        E     F     G               H    
1   club            Sc    Rank  UniqRk           Pos   Club            Score
2   third-equal#1   80    3     79.999980 1     1     best            100  
3   second          90    2     89.999970 2     2     second          90   
4   third-equal#2   80    3     79.999960 3     3     third-equal#1   80   
5   best            100   1     99.999950 4     3     third-equal#2   80   
6   worst           70    5     69.999940 5     5     worst           70   

Columns A and B are our calculated scores, column E is the order in which clubs will be output in the final table. The other formulae are as follows:

C: =RANK(B2,$B$2:$B$6)      # what it says, with ties both getting the lower number
D: =B2-ROW()*0.00001        # score, modified slightly to ensure uniqueness
F: =SMALL($C$2:$C$6,E2)     # first output column, ranks including ties
G: =INDEX($A$2:$A$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))
                            # club name for position, using the modified score in D
H: =INDEX($B$2:$B$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))
                            # as G, but indexes into scores