Best 5 of 6

[sooty]

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

 

[Domski]

Hi,

I was assuming that this scoring system replicates a manual system that already exists following specific rules.

Do you not have guidance notes from a governing body or something as to how scores should be calculated or is this something that you're setting up yourself?

Dom

 

[sooty]

This is something that I'm setting up myself, as historically, it's all been done by hand and it can get very long-winded and mistakes regularly happen.

Having everything on computer makes life soooo much easier

A competitors average over a season dictates which division he competes in, and he/she can get promoted (or demoted) into the National League Table (a bit like the football leagues) so it's quite important.

The governing body use the best 5 out of 6 scores to allow for an 'off day' and give a fairer indication of performance for County Team selections.

Once I've set it all up and proved that it works, I could offer it out for other clubs to use.

Iain

 

[Dan Waterloo]

The LARGE function may be able to help you out here. If "the last five rounds" means Round 6 to Round 10 then the formula you want is

=average(large(O8:Q8:S8:U8:W8, 1), large(O8:Q8:S8:U8:W8, 2), large(O8:Q8:S8:U8:W8, 3), large(O8:Q8:S8:U8:W8, 4), large(O8:Q8:S8:U8:W8, 5))

If "the last five rounds" means the last five rounds that have been shot then you need to figure out how to move the reference array around. Right now it's O8:Q8:S8:U8:W8. That's rather clumsy to use because it consists of non-contiguous cells. You might consider using a second worksheet (Scratch) where the non-contiguous cells on the your original worksheet (Data) get set right next to each other. E.g.,
Scratch!A8 is =Data!E8
Scratch!B8 is =Data!G8
Scratch!D8 is =Data!I8
Scratch!E8 is =Data!K8
Scratch!F8 is =Data!M8
Scratch!G8 is =Data!O8
Scratch!H8 is =Data!Q8

Then you could use the OFFSET function to determine the range that you want to run the LARGE function on.

=AVERAGE(LARGE(OFFSET(Scratch!A8, start, 1, 5), 1),
LARGE(OFFSET(Scratch!A8, start, 1, 5), 2),
LARGE(OFFSET(Scratch!A8, start, 1, 5), 3),
LARGE(OFFSET(Scratch!A8, start, 1, 5), 4),
LARGE(OFFSET(Scratch!A8, start, 1, 5), 5))

where start tells you how many cells to the right of A8 you should start the range.

 

[Dan Waterloo]

I'm not sure whether you should use the values in E8, G8, I8 etc. But you want to use the base scores not the running average scores.

 

[Domski]

It's seems like a bit of a complicated layout to be performing anything more than simple calculations on. I would suggest that you might be better off having a background sheet that takes the scores entered performs all the calculations and then feeds the results back into the score sheet.

Working out things like averages on non-contiguous cells is only going to over-complicate your formula.

Dom