## Top ten ordering in Excel based on complex team rules

By : Vagnerr
Source: Stackoverflow.com
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.

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

can this be done easily as a series of Excel formula

(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
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
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),
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:

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