Top ten ordering in Excel based on complex team rules

By : Vagnerr

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

| 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.

By : Vagnerr


What I do is lame, but it works.

Just make a new column then insert this formula =If(a1=N,b1,0) where A1 is criteria column, N is criteria and B1 is in the column that you are trying to get the large from. Then I just do the large formula in another column.

Sometimes I get all fancy and instead of rolling out a N, I will make it say $C$1, then spell out the criteria in that cell.

The perfect answer would be to have Microsoft add in a largeifs (please read this Microsoft)

By : drew

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
            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.

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
 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


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:


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:


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:


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

This video can help you solving your question :)
By: admin