Is this impossible, logic genius required.

I am sure that this is possible, because in Excel everything seems possible, but this is way beyond me and requires a logic genius as much as an Excel one.

I am a member of an athletics club and I have been charged with working out the long distance running club championship.

In the club Championship there are going to be 16 races. The athletes will be awarded points for their finishing position. 15 points will go to whoever finishes first, 14 for second and so on. If there are more than 15 runners, which there will be in some races then whoever finishes first will get a number of points equivalent to the number of runners running. But this bit is irrelevant to the problem

What I need is a formula which calculates the 7 highest scores for each runner.

Unfortunately there is more to it than that. 5 of the races will be a short distance on road, 5 will be a short distance off road, 3 will be a medium distance off road, 1 will be a long distance on road, and 2 a long distance off road.

The following criteria will apply for the full 7 scoring races.

At least 1 race must be long distance.
At least 1 race must be medium distance.
There must be no more than 5 short distance runs counted.
There must be no more than 3 short distances on rod counted.
2 of the races must be off road of different categories. i.e. long and medium.

A runner who does less than 7 races will still score in the championship but he must still satisfy the above conditions. So for example a runner who did only 5 short road races could only score for 3 of them.

Sorry this is very complicated.

To make it easier for me I have arranged the runs in category order rather than chronological order.

So for the first runner in cells c3-g5 are the five short runs. In cells h5 to l5 are the 5 short off road runs, in cells m5 to o5 are the3 medium off road runs. In cell p5 is the long on road run, and in cells q5 and r5 are the long off road runs.

In cell S5 I need to find a formula which calculates the athletes 7 best (or less) scoring events that takes into account all the above conditions from the point of view that it does not matter if he does not do a long run as long run as long as he has no more than 3 short runs on road counting in his total or whatever.

This is beyond my brain power. Is it possible?

file attached

Originally Posted by oldchippy

Can you confirm the following,

What I need is a formula which calculates the 7 highest scores for each runner.

5 of the races will be a short distance on road, - Count no more than 3
5 will be a short distance off road, - Count no more than 5 short on or off road
3 will be a medium distance off road, - Count 1
1 will be a long distance on road, 2 will be long distance off road.- Count 1 off road only

If that's OK try this in S5

=LARGE(C5:G5,1)+LARGE(C5:G5,2)+LARGE(C5:G5,3)+LARGE(H5:L5,1)+LARGE(H5:L5,2)+MAX(M5:O5)+MAX(Q5:R5) and copy auto-fill down

As daddylonglegs says it is a little more complex than this.

A runner must do at least 1 medium off road run and at least 1 long run, but he can have more than 1 scoring run from each of those catagories. For example if his 7 highest scoring runs consisted of all the 3 medium runs and all the 3 long runs and just one of the short runs then all the criteria would be met and all 7 scores would count.

On the other hand if a runner did 3 of the short runs on road, all of the medium runs and the long on road run then only his 6 highest scores would count because he did not meet the criteria that 2 of the runs must be off road of different categories and he will only have done medium off road runs.

I accept that this may require more than one formula but if any one could come up with a solution or suggestion using one or more formulas I would be grateful.

The problem is that there could be hundreds of runners taking part and I was hoping for some sort of automation to reduce human error of manually processing each runner and of course to save time. I thank you all for your efforts.