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