Best 5 of 6

sooty

New Member
Joined
Jul 30, 2007
Messages
5
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 :confused: ) 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
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Jan 4, 2007
Messages
876
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
Joined
Jul 30, 2007
Messages
5
WOW that was rapid :biggrin:

I certainly wasn't expecting a response that quick (y)

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

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
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Have a look at downloading Colo's HTML Maker utility . The link's at the bottom of the page.

Dom
 

sooty

New Member
Joined
Jul 30, 2007
Messages
5
Yeah........... spotted it just after I hit the submit key :oops:

Feel a right pratt now. :rolleyes:

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

Iain
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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 :biggrin:

I certainly wasn't expecting a response that quick (y)

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

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
Joined
Jul 30, 2007
Messages
5
Ok fellas...many thanks for all the quick responses (y)
Looking at them, I'm starting to feel a right Thicko :rolleyes:

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
Joined
Jan 18, 2005
Messages
7,292
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
Joined
Jul 30, 2007
Messages
5
Hi Dom.

Good question and one I hadn't thought about.

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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top