# Best 5 of 6

#### sooty

##### New Member
Hi guys.

I stumbled across this site purely by accident and I'm hoping someone can help me out with a little problem I've got.

I've made some score sheets for the shooting team I'm a member of, that keep a record of all the individual points scored over the course of a season (eleven rounds).

It took me a while (I'm still a relative novice when it comes to excel) but I got there in the end.

As the scores are entered on the sheet, the average of the last six scores are calculated and displayed at the end of each row as a percentage.
This is a 'rolling' average, ie rounds 1 to 6 will give an average, then 2 to 7, 3 to 8, etc.

The problem is, I now need to work out a formula that will take these last six scores and give me the best five out of the six.

I hope I've explained it well enough (probably not ) so does anyone think they can help?

It would be greatly appreciated.

Iain

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### dave3009

##### Well-known Member
Hi Iain

Welcome to the Board

Say your rolling scores run from D2:D12 (10 for this example)

in E2 have =RANK(D2, \$D\$2:\$D\$12, 1)

Copy this and paste it down to E12

HTH

Dave

#### Dan Waterloo

##### Well-known Member
Hi sooty,

It sounds like you can solve your problem if you can sort the list. One problem might be that changing the order of items in the list would change the values of the formulas. To avoid this, create a new worksheet. Then Copy your current worksheet and Paste Special as Values on the new worksheet. (You can either use /Edit/Paste Special or right click and select Paste Special. Values is one of the radio buttons.)

The formulas in your dataset should now be replaced by their values. Check it out. You also still have your original worksheet to go back to.

Now select a cell in the dataset on the new worksheet. /Data/Sort and now you likely want to sort by Name (ascending) and then by Rolling Average (descending). This would keep all six rolling averages but would show which five were the highest.

#### sooty

##### New Member
WOW that was rapid

I certainly wasn't expecting a response that quick

Unfortunately, things aren't as easy as they seem (Ijust knew that my explanation wasn't detailed enough )

The layout of the scoresheets is such that the results I need to look at aren't in a continuous row and I can't figure out any other way of laying them out and maintain clarity for the guys to understand them.

I know it's a bit of a cheek but is there any way I could post up a copy of an example sheet so that you could see what I'm doing?

Iain

Hi,

Dom

#### sooty

##### New Member
Yeah........... spotted it just after I hit the submit key

Feel a right pratt now.

I'll give it a go but don't wait up just for me

Iain

#### tusharm

##### MrExcel MVP
Since you know how to calculate the average currently, you can do the following: =(SUM({current range reference} - MIN({current range reference}))/MAX(1,(COUNT({current range reference})-1))

The above will give you best 5 out of 6 and, if you are calculating the average of all N when N <6 then it will give you best N-1 out of N.

WOW that was rapid

I certainly wasn't expecting a response that quick

Unfortunately, things aren't as easy as they seem (Ijust knew that my explanation wasn't detailed enough )

The layout of the scoresheets is such that the results I need to look at aren't in a continuous row and I can't figure out any other way of laying them out and maintain clarity for the guys to understand them.

I know it's a bit of a cheek but is there any way I could post up a copy of an example sheet so that you could see what I'm doing?

Iain

#### sooty

##### New Member
Ok fellas...many thanks for all the quick responses
Looking at them, I'm starting to feel a right Thicko

I've managed to sort out the HTML maker (eventually) so a snapshot of my chart should appear below.

A quick word of explanation might help so here goes...........

1. Pages are in landscape format and are printed out so that they can be pinned on the notice board.

2. Once a competitor has shot his round, he will estimate his own score before sending the target card away for verification. He pens in this score in the top left box of this scoresheet (cells D7, F7, H7, J7, etc in this example). Note that scores are calculated on the basis of points dropped out of 100 rather than actual points scored.
Worksheet.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
5NameIndividualDiv/No.Rd1Rd2Rd3Rd4Rd5Rd6Rd7Rd8Rd9Rd10Last5Averages
6
7Comp#12596.5197.3397.3297.4197.4198.097.798.098.0###97.4
82###5###1###3###2###2###############
Worksheet

3. Once the score has been verified (anything up to 2 weeks later), it's entered into the lower right box of the scoresheet ( cells E8, G8, I8, K8, etc)

4. In order to get a running average of the scores, I've used the following formulae and set-up:

in cell G7 =100-AVERAGE(E8,G8) then cell F8=G7

in cell I7 =100-AVERAGE(E8,G8,I8) then cell H8=I7

in cell K7 =100-AVERAGE(E8,G8,I8,K8) then cell J8=K7

in cell M7 =100-AVERAGE(E8,G8,I8,K8,M8) then cell L8=M7

Now the formula starts to 'roll' along

in cell O7 =100-AVERAGE(G8,I8,K8,M8,O8) then cell N8=O7

in cell Q7 =100-AVERAGE(I8,K8,M8,O8,Q8) then cell P8=Q7

and so on through to the last round.

In the Average cells at the end of the sheet (Z7 ?), I've used the following:

=IF(Y8>0,X8,IF(W8>0,V8,IF(U8>0,T8,IF(S8>0,R8,IF(Q8>0,P8,IF(O8>0,N8,IF(M8>0L8)))))))

This gives me a constantly updated average of the scores (or seems to :wink: ) every time the sheet is amended.

Now to the problem.............(bet you thought I'd never get round to it did you? )

The average at the end of the sheet gives me a 'Total' average of the competitors last 5 rounds.

What I really need is the average of the best 5 out of the last 6 rounds.

Hope this makes things clear and I haven't confused the hell out of everyone.

Iain

#### Domski

##### Well-known Member
Hi,

Sorry if I've missed something but, if possible, how do you work it out if the bottom two scores are the same?

Dom

#### sooty

##### New Member
Hi Dom.

If the 6 scores were, for example, 2, 1, 5, 5, 3, 4, I would assume that the calculation would look at the two lowest (pair of 5's) and just use one of them.

I'm probably wrong but there's bound to be someone on here that knows the answer (and it'll be way too complex for my simple brain to figure out )

Iain

Replies
25
Views
1K
Replies
4
Views
694
Replies
0
Views
572
Replies
7
Views
641
Replies
7
Views
540

1,181,055
Messages
5,927,863
Members
436,573
Latest member
CMR237

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back