Is this impossible, logic genius required.

Can you zip your spreadsheet and post it here so that we can come up with an answer for you. Go to My Documents or wherever you have your file, right-click and Send to > Compressed file, then attach that to your post.

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

Oldchippy

Slightly shorter

=SUM(LARGE(C5:G5,{1,2,3}))+SUM(LARGE(H5:L5,{1,2}))+MAX(M5:O5)+MAX(Q5:R5)

VBA Noob

Thanks VBA, thought someone would be able to make it shorter

DLL is looking at it now.

Bet he shortens it

He always does. His a formula grand master.

Think I learnt that one off him

Think he should set up his own website just on formula's

I'd pay to see that

VBA Noob

Originally Posted by Quaisne

If Tony Bell did those races he would fall foul of the criteria as only 3 short road races can count so only 5 of those 7 could count for him. If he had only done 7 races then he would only have 5 scoring runs he would have to drop his 2 lowest scoring short road runs. If he did H5, I5, J5, K5, L5, O5 and Q5 it would count, as 5 short off road runs are permitted by the criteria.

The person who passed this task over to me knew what she was doing.

Hi,

not sure that I've understood all of the questions, but the attached will allow you to select the best 5, 3 from each category, other conditions you will need to further specify.

Columns S through AC can be Hidden columns to preserve the original look of your sheet.
Column S is for Rule 1
Column T is for Rule 2
Column U, V & W for Rule 4
Column X, Y & Z for Rule 5
Column U to Z for Rule 3

Columns AB & AC are additional points possibilities.

Let me know how this goes to suit your needs.

note the formula is only complete on Row 5 - - formula fill to other rows.

Also noted, the contra to Men is Women, and to Ladies is Gentlemen.

hth
---

Brian,

Would this be shorter

=SUM(LARGE(U5:Z5,{1,2,3,4,5}))

VBA Noob

You do like a challenge Bryan - I think you may have crack it there, hopefully Quaisne will tell us

Originally Posted by VBA Noob

Brian,

Would this be shorter

=SUM(LARGE(U5:Z5,{1,2,3,4,5}))

VBA Noob

VBA Noob,

it sure would, and I guess in AD5

=SUM(S5,T5,AA5:AC5)

would be also.

and hi oldchippy.

cheers
---

Lol

Didn't spot that one

Where's DDL when you need him

VBA Noob

Even shorter or is it

=SUM(S5:T5,AA5:AC5)

VBA Noob

Say you move the columns then you could use

=SUM(Y5:AC5)

VBA Noob

Originally Posted by VBA Noob

Lol

Didn't spot that one

Where's DDL when you need him

VBA Noob

I think it's in error,

Rule 4 - No more than 3 Evening League
Rule 3 - No more than 5 category S

that is, if the member does no ev they score the 5 other category, but a maximum 3 from ev.

hth
---
added, missed your post VBA Noob, was busy fixing column location to agree with Rules, it was set (in error) to a max of 3 daytime OR.
also added, I thought the comma , was shorther that the colon : (the colon is taller)
---

Originally Posted by Quaisne

It is nearly there that. Thank for your efforts.

However a runner could do the 3 medium races plus the 3 long races and just 1 of the short races and that would qualify all conditions and say he got 10 points for each he should score 70 points.

He would have at least one L race
He would have at least 1 M race
He would have no more than 5 category S races
He would have no more than 3 EL races
He would have run at least 2 different OR races.

But he only scores 50 on that spreadsheet.

A runner does not have to run any EL races and can still satisfy all the conditions but in that case he would have to run one S O/R race.

There is only one scenario where rule 5 would disqualify a runner from 7 scoring runs if he did 7 races and that would be if he did 3 EL races, the one L race that is not on road, and the 3 medium runs. In that case he would have done 7 races but only 1 OR category.

I hope I am not confusing the issue further.

With regards to the ladies and gentleman issue. I did originally have men and women on the spreadsheet but changed it because it is a quirk in running circles where I live that men runners are never referred to as gentlemen and women are nearly always referred to as ladies.

As I have said Bryan, you are very close and I am grateful.

The rules you have set appear to have been met in version 2, if a member has C1 and C2 then rule 5 appears to be met (2 OR races), if the member has either C1 or C2, AND has K:L then the condition (2 OR) appears to be met, in short, if a member matches other rules then Rule 5 appears superfluous.

My sheet does not (yet) attempt to ascertain who qualifies and who doesn't, the conditions specified in rows 8 to 11 seemed to be guides to the filling-in of points rather than the inclusion/exclusion of eligibility, however, to correctly complete the table, if you fill in a number of different sample scores, and note in column AB for those scores, who fails to qualify (ie, that a person can win columns C D E F G K L M N O & S but fails to qualify) then the setting can be automated.

Cheers
---

Originally Posted by Quaisne

Thanks for your continued help

It is possible for rule 5 to become valid if a runner does
3 of the short EL races
All three medium o/r races
and the Burn Valley L race which is a road race.

In that scenario he will have completed 7 races but only 1 will be an O/R category.

I think this is even more complex than I could have imagined.

Only 7 of the runs can count for a runner. His best 7 scores as long as they meet the conditions.

my point was that such a runner failed Rule 1, and failed Rule 2, therefore Rule 5 doesn't count, by failing Rules 1 & 2 the runner fails to qualify. A runner can pass (either Rule 1 or Rule 2) together with a S/OR (columns K to O) to pass Rule 5 but fail to qualify. (added, just noticed that Rule 1 also includes Burn, but Rule 2 and the best of 5 still obviate Rule 5)

It is not so difficult, the only hard part was the '3 el best of 5' which is overcome by using the hidden columns H I J and taking the best of 5 from H to O.

If you enter sample scores into the file I last sent (Championships2.xls) and not which pass or fail the rest of the sheet can be completed.

---

Originally Posted by Quaisne

A runner who does just 1 race will still register a score in the championship.

However if he does not satisfy the first 2 rules he will not score the maximum permitted races, i.e 7, even if he does 10 races. In theory he would still have a chance, if very remote, of winning the championship as all races that met the conditions would score.

The sheet works brilliantly for any runner that completes 5 short races that conform to the last 2 conditions.

A runner though could score the 7 maximum scores if he only did 1 short race

If a runner does only 6 races, those being the last 6 on the sheet i.e. Meanwood Trail, Mytholmroud, James Herriot, Burn Valley, Trollers Trot and Harewood 10, and he scores 10 points for each race he should score 60pts. The sheet actually only gives him 20.

If he further does any one of the 10 short races and scores another 10 points he would have satisfied all conditions and should score 70 points but he only scores 30.

Similarly if he did the 6 last races plus 3 of the Evening League races and one of the Short off road races he would stuiill only register 60 points on the sheet instead of 70.

Rule 5 would apply if a runner did the 3 medium off road runs, plus Burn Valley, plus 3 of the short evening league races. He would have done 7 races which is the highest number of races a runner can score from in the championship. He would have met all the conditions 1-4, but he would not have done 2 different O/R categories. He will only have done a medium off road run but not a long one or a short one. So rule 5 would allocate him60 points provided he scored 10 points for each race.


Rules 3 and 4 set a maximum limit to the number of short runs that can be done. There is no minimum, although to satisfy all the conditions to score maximum points from the 7 permitted scoring races a runner would need to do at least 1.

For the points total, AA5 should be amended to =SUM(P5:U5,X5) and that formula filled down to row 60, however on the sample supplied they will all total to zero except for row 5 which remains at 35.

Columns H-J need to be un-Hidden and the formula from I5 and J5 formula filled down to row 60.

As previously stated, I have not yet attempted to set the 'qualifies/not-qualifies' as although the rules may be clear to you they do not appear clear on reading. Your statement 'A runner who does just 1 race will still register a score in the championship' and then 'If a runner does only 6 races, those being the last 6 on the sheet i.e. Meanwood Trail, Mytholmroud, James Herriot, Burn Valley, Trollers Trot and Harewood 10, and he scores 10 points for each race' seem confusing when reading 'score the 7 maximum scores' and Rules 1 & 2 At least 1 must be category L & At least 1 must be category M, hence my request for a few sample scores to help clarify what points are totalled and when, and who 'qualifies' and how.

perhaps in version 3

---

Originally Posted by Quaisne

I thank you for your help Bryan and I hope I am not wearing your patience too thin. I have tried to expand on the rules more below.

I have also tried putting data in version 3 and the following yielded incorrect final points
If you enter

did you mean if a secretary enters

16 in C5, 15 in d5, 14 in e5, 13 in f5, 12 in g5, and carry the sequence on all the way to U5 where you enter 1. The total points here should be 77 but it scores 37 in version 3. The scores that should count are c5,d5, e5, k5, l5, p5, q5.

disagree, according to Rule 1 at least one race must be L or L/OR, C5:Q5 does not seem to conform

Similarly if a runner scored 10 points in C5, 9 in D5, 8 in E5, 7in L5, 6 in M5, 5 in N5, 3in Q5, 2 in R5, AND 12in S5 he should score 55 points but version 3 gives him 51. Interestingly in that scenario if you change the 2 in R5 back to 0 then the score does go to the correct total of 55.

my mind does not go so far as to be able to mentally apply such scores under the rules and formula as set

AN ATTEMPT AT FURTHER RULE CLARIFICATION

A runner can do all 16 races but only the 7 highest scores that satisfy the below 5 conditions, will count in the club championship scoring. A runner can do less than 7 races and still score points but of course he would have a reduced chance of winning the championship.

If a runner only does say 6 runs they would all count unless they broke any of the below rules. For example If those 6 runs were 5 short Evening League runs and 1 other then only 4 of the scores would qualify as rule 4 states no more than 3 evening league races.

S/EL= An evening short run which will be on the road.
These are Otley, Richmond, Harrogate, Wetherby and Menston

S/OR = A short run off road
These are Guiseley Gallop, Pen Y Ghent, Wakefield, Pudsey, Knavesmire

M/OR = A medium run off road
These are Meanwood Trail, Mytholmroyd, James Herriot Trail

L = A long run off road
This is Burn Valley

L/OR = a long run off road
These are the Trollers Trot and Harewood 10.

There are therefore 3 OR categories S/OR, M/OR and L/OR
and 2 Road only categories S/EL and L

Rule 1
At least 1 must be category L or L/OR
So that is Burn Valley, The Trollers Trot or Harewood 10
If a runner did all of these races and each one was in his top 7 highest scores all 3 would count to his total.

Rule 2
At least 1 must be category M.
All the medium runs are off road and so are M/OR
So this group includes Meanwood Trail, Mytholmroyd, James Herriot Trail
If a runner did all of these races and each one was in his top 7 highest scores then again all 3 would count to his total.


Rule 3
No more than 5 category S
This rule means that no more than 5 races from the S/EL runs and the S/OR runs can count in a runners top 7 scores. So that is the Otley, Richmond, Harrogate, Wetherby, Menston, Guiseley Gallop, Pen Y Ghent, Wakefield, Pudsey and Knavesmire runs.

Rule 4
No more than 3 Evening League races.
These are the S/EL runs namely the races at Otley, Richmond, Harrogate, Wetherby and Menston.
This rule means that only 3 races from this category can qualify for a runners top 7 scores.

Rule 5
2 must be O/R of different categories.
There are 3 OR categories, S/OR, M/OR L/OR.
A runners has to race over 2 of these categories to have all his 7 scores count.
There is one scenario only where a runner would fall foul of this rule. That is if he did 7 races 3 of which were S/EL, 3 were M/OR and one was L (Burn Valley.) He would have done 7 races meeting conditions 1-4 but failing condition 5.

the rules are nice, but the rules are for runners and race organisers, and I have my doubts that either oldchippy or myself will ever be either in your organisation.

I am attempting to write and test Formula, and for that I need numbers in columns and the expected results, hence my three-times asking for sample data.

in cell AJ5 put the following

=SUM(W5:Y5,LARGE(Z5:AG5,{1,2,3,4}))

and formula fill that to row 60.

see how that fares.
---

I've been following this one with interest - WELL DONE BRYAN!

And Quaisne,

and that scenario is very unlikely to occur anyway

if someone does shoot them!

Originally Posted by Quaisne

Thanks for your time Bryan. That appears to work on all but one scenario I tried and that scenario is very unlikely to occur anyway.

Your help has been very much appreciated.

what is the scenario?

also, you did not confirm the Rule 1 query raised in my last post.

---

Originally Posted by Quaisne

Bryan you are correct with your point about rule 1. I made a mistake my excuse being that I posted that late at night, English time, without proof reading it and being tired from doing an evening training session, for my first marathon, I just wanted to crawl into bed. Sorry about that I need to proof read before I post.

The score should of course be 75 not 77, with S5 couning instead of Q5.

The only scenario that I have found that the last version does not work on is in that troublesome rule 5's teritory.

If a runner does just 7 races and those races are all the 3 medium runs, the Burn valley Long run, and 3 Evening League runs. He has satisfied the first 4 rules but has not done 2 different off road distances. So he can only score from 6 runs. He would in this situation drop one of his his lowest scores.

If that runner did the 7 runs described above plus 1 of the short off road runs, he would have to drop one of his lowest scores and substitute that with whatever he scored in the short off road run. So say he scored 10 points for the first 7 described races and only 5 in the Short off road run he would register 65 points not 70.

in R5, overwrite the defunct formula (that gives AG etc) with

=IF(LARGE(K5:O5,1)>0,1,0)+IF(LARGE(P5:R5,1)>0,1,0)+IF(LARGE(T5:U5,1)>0,1,0)

in H5, amend the formula to

=COUNTIF(W5:AG5,">0")

in AI5 put

=IF(OR(AH5<7,V5>1),0,LARGE(Z5:AG5,4))

in AJ5 amend the formula to subtract I5,

=SUM(W5:Y5,LARGE(Z5:AG5,{1,2,3,4}))-AI5

I presume that if the runner does only 6 races then Rule 5 does not apply, the (missing) 7th race being assumed to be their penalty.

added, also, head column V as R5
head column AI as R5 Deduct

The formula at Z5 to AG5 can now be simplified, there is no reason to allow for Rule 5 within these formulae.

Z5 =

=LARGE(S5:U5,2)

AA5 =

=LARGE(S5:U5,3)

AB5 =

=LARGE(P5:R5,2)

AC5 =

=LARGE(P5:R5,3)

AD5 =

=LARGE(H5:O5,2)

AE5 =

=LARGE(H5:O5,3)

AF5 =

=LARGE(H5:O5,4)

AG5 =

=LARGE(H5:O5,5)


Let me know how this goes
---

Originally Posted by Quaisne

Thanks for your continued help Bryan. I am not quite sure about the first instruction though -

"in R5, overwrite the defunct formula (that gives AG etc) with

=IF(LARGE(K5:O5,1)>0,1,0)+IF(LARGE(P5:R5,1)>0,1,0) +IF(LARGE(T5:U5,1)>0,1,0)"

Unless I am misunderstanding something, there is no formula in R5 in the 3rd update as that is for data entry.

You are correct with the assumption -

"I presume that if the runner does only 6 races then Rule 5 does not apply, the (missing) 7th race being assumed to be their penalty"

that was V5 (not R5)

---

Originally Posted by Quaisne

The next instruction

"in H5, amend the formula to

=COUNTIF(W5:AG5,">0")"

Do you mean I should substitute the contents of H5 which is

=LARGE(C5:G5,1)

with the above.

If I do that it changes the total in that cell to 0, whatever data is put in, and gives an incorrect total in AJ5.

that should be AH5 (not H5)

---

Originally Posted by Quaisne

If a runner scores say 10 points for c5, d5,e5, p5,q5,r5 and s5 the new spreadsheet now works and correctly allocates the runner only 60 points.

If that runner scores 10 or more points in any other race it again gives the correct answer.

If a runner does the above 7 races and scores 10 in each one and also does one other race where he scores say 5 points he should register 65 points. The spreadsheet gives him 70 though. It appears the only remaining flaw is this scenario.

The system is not yet tested with a sufficient range of test scores.

If a runner scores say 10 points for c5, d5,e5, p5,q5,r5 and s5, the runner scores 60 points.
If the runner scores a further 5 points at races f g k l m n o then the runner still scores 60 points.
If the runner scores 5 points at s t or u then the runner should score 65 points.
Currently t and u work correctly for 65 points, but s produces a race count error and removes a '10' score, showing 55 points.

Correcting V5 to

=IF(LARGE(K5:O5,1)>0,1,0)+IF(LARGE(P5:R5,1)>0,1,0)+IF(LARGE(S5:U5,1)>0,1,0)
corrects this error, but without adequate test data I do not know what other problems this may introduce.

It does not, it just ruins R5


Where the extra 5 points are scored in column s I am investigating.

note, the formula currently showing at V5 to AJ5 are:

=IF(LARGE(K5:O5,1)>0,1,0)+IF(LARGE(P5:R5,1)>0,1,0)+IF(LARGE(T5:U5,1)>0,1,0)
=LARGE(S5:U5,1)
=LARGE(P5:R5,1)
=LARGE(H5:O5,1)
=LARGE(S5:U5,2)
=LARGE(S5:U5,3)
=LARGE(P5:R5,2)
=LARGE(P5:R5,3)
=LARGE(H5:O5,2)
=LARGE(H5:O5,3)
=LARGE(H5:O5,4)
=LARGE(H5:O5,5)
=COUNTIF(W5:AG5,">0")
=IF(OR(AH5<7,V5>1),0,LARGE(Z5:AG5,4))
=SUM(W5:Y5,LARGE(Z5:AG5,{1,2,3,4}))-AI5

hth
---Perhaps for AH5
=COUNTIF(W5:AG5,">0")-IF(S5>0,1,0)
but this, whilst giving 60 / 65 points as required, is as yet untested.
---

added Rules question, If a runner qualifies Rules one, two and five on races scoring 10, 10, 10, 10, 10, 10, and 5, and has an additional 8th race where the runner scored 10, then are the championship points 75, 70 or 65?
the current setup is for 65.
---
---

Originally Posted by Quaisne

It is now very close to being cracked. I have attached the updated spreadsheet with some sample data in with extra columns showing what should have been scored for those that yielded an incorrect total.

With regards to your question

"added Rules question, If a runner qualifies Rules one, two and five on races scoring 10, 10, 10, 10, 10, 10, and 5, and has an additional 8th race where the runner scored 10, then are the championship points 75, 70 or 65?
the current setup is for 65."

If the 7 scores of 10 satisfy all the rules then the runner will score 70 points. He would not score 75 under any circumstance as only his highest 7 scores can count no matter how many races he runs. He would only score 65 points if the 7 races where he scored 10 points did not satisfy all the rules and 6 of the 10s and the 5 did.

I am amending the rules to conform to the conditions shown. Rule 1 is apparently not a rule in it's own right but more a leftover of Rule 5 (ie, a score from category L may, or may not, happen depending upon Rule 5).

Please check that Row 9, with scores from s/el 12, 18, 7, m/or 9, 13, 21, and Burn 15 Score 95 is not 'correct' but fails Rule 5, must have 2 'OR' categories.

Row 13, reported as 'correct', from your scores s/el 11, 14, 16 m/or 10, 10, 10, Burn 10 fails Rule 5
Row 14, reported as 'correct', from your scores s/el 10, 10, 10 m/or 10, 10, 10, Burn 10 fails Rule 5
Row 16, reported as 'in error', from your scores s/el 16, 15, 14 s/or 2, m/or 13, 12, 11 fails Rule 1
Row 17, reported as 'correct', from your scores s/el 16, 15, 14 s/or 10 m/or 13, 12, 11 fails Rule 1.

---

Originally Posted by Quaisne

It is now very close to being cracked. I have attached the updated spreadsheet with some sample data in with extra columns showing what should have been scored for those that yielded an incorrect total.

With regards to your question

"added Rules question, If a runner qualifies Rules one, two and five on races scoring 10, 10, 10, 10, 10, 10, and 5, and has an additional 8th race where the runner scored 10, then are the championship points 75, 70 or 65?
the current setup is for 65."

If the 7 scores of 10 satisfy all the rules then the runner will score 70 points. He would not score 75 under any circumstance as only his highest 7 scores can count no matter how many races he runs. He would only score 65 points if the 7 races where he scored 10 points did not satisfy all the rules and 6 of the 10s and the 5 did.

Hi,

see the comments re Rules in the previous post.

Attached is a scratch edition, I have re-written all formula to match the data supplied, however 5 rows of data do not appear to match the Rules, also, in items such as Row 10, what gives the precedence for H10 to be included in the total as opposed to (say) O10 or J10

more tomorrow (Cinderella has left the building)
---

finally, something worth testing.

try a few more results in this version.

hth
---
note, you can re-widen your columns after testing, it's just easier to see the score.
---
what the Formulae do:

Column T determines if Q-R (L-OR) is required under Rule 5
Column U is the score if required

Column V determnes if Burn will be required by Rule 1, and sets the score
Column W determines if Burn will be required anyway, and sets the score

Column Y determines if M-O will be required under Rule 5
Column Z is the score if required

Column AB determines if H-L required under Rule 5
Column AC is the score if required

Column AD, AE and AF count the zero scores when Rule 5 might require a valid score
Column AG counts Rule 5 failures, this to reduce the number of 'best of' scores from the remaining pool

note, the above scores are required by Rule 5 and Rule 1
the remaining scores are then pooled for the 'best of' to the number of scores up to 7

Column AH-AX are the rest of the scores for the 'best of' 7 less those scores already allocated for Rule 5 and Rule 1

Column AH is Burn leftover (ie, if not already selected)
Column AG-AH are the L-OR leftovers

Columns AL-AN are the s/el 3 races

columns AP-AT are the s-or leftovers

Columns AV-AX are the m-or leftovers

Column AZ counts the allocated points (of which one may be a zero under Rule 5 = 2 x 'OR' races), and adds the Largest(s) from the pool to a maximum of 7 races.

If you feel that you do not fully understand the workings of these formule, don't despair, you are not alone.

---

Originally Posted by Quaisne

In row 10 the lowest score needs to be removed. Nothing gives H10 precedence over any other score of 10 as long as the rules are not broken.

ok so the figure will be picked from the 'pool'

Rows 16, and 17 - you are correct the scores should be as shown in the new spreadsheet.

Rows 13 and 14 are correct

I presume that you mean 'are now correct in version 7'

because in these scenarios the runner has completed 7 races but falls foul of rule 5 so he must drop a race. He would drop his lowest score. He would score in only 6 events but would have the opportunity to score 7 races if he did another run that satisfied rule 5.

This is complicated to explain but if a runner does just 6 races then all those races would count for his championship points PROVIDED if he did one more race he could satisfy all the rules. So clearly if he did 5 short Evening League races and one of the short off road races by doing 1 more run he could not satisfy all the rules and therefore could not score for all these 6 races. So in this scenario he would have to drop 2 races. The new spreadsheet copes with this.

good to see

I will test the new spreadsheet thoroughly over the weekend as my running commitments mean I will not be afforded the appropriate time beforehand, but t is looking as though you have cracked it.

I guess stuck between the Dales and the Moors you would need to keep running this time of year to keep warm. I used to travel between Mansfield and Barnard Castle so know the countryside a little.

I will let you know whether or not I find any errors.

May I thank you for all the time and effort you have put into this and I will certainly be recommending this web site to anyone who has an Excel problem, it is most certainly the font of all Excel knowledge.

I look forward to the results of your testing, after all, not every formula has 26 helper columns

I wonder now how many of those formula could be shortened, or how many columns are no longer needed, but have no intention of changing them, and especially not before final testing.

Good luck to yourself and all the Nidd Valley runners.
---

Originally Posted by Quaisne

I have had a go with some test data and I have only found one minor error.

Although there are 4 lines which show an error it appears to be the same one.

See attached spreadsheet for details.

Hi,

fixed, you can hide columns T through BD.

I cannot agree with rows 10 and 21, your score appears incorrect, can you check these two.

See Conditions page for the list of 'how it did'.

Cheers
---
added fixed error in column AC, decorative changes for Rules helper columns.
---

Originally Posted by Quaisne

There appears to be slight anomalies with rule 1 and 2 under certain circumstances. Spreadsheet with details attached.

seems to be fixed except for the two I disagree your scores, see the comment boxes.

The number of helper columns, from T to BM has now blown out to 40, but at the moment I am more cconcerned with getting the scores correct, and in the easiest methods possible, as someone may need to amend these some day.
You can hide columns T to BM and ignore the Notes on the conditions page.

Column BP highlights if the Score (BO) does not equal your expected (BQ)

In the helper columns the figures highlighted are figures required by the rules, these are from conditional format for the block as shown.


The 'Adjusted Best Of' for the pool figures is to avoid a 7-If limit problem, it also makes the formula easier to read, even if the 'Top 6' has either 6 or (due to Rule 1) only 5 and a zero.

Back to you for more testing.

Cheers.
---

Originally Posted by Quaisne

You were correct with the 2 you disagreed over my totals with.

I have found a couple more scenarios where the spreadsheet seems to give erroneous totals.

The first one may complicate things. On line 39 the runner has completed 8 races but only 6 can count. This has been worked out correctly by the spreadsheet. However, on line 40 the runner has completed the same 8 races plus one other in which he only scored 4 points but, as he has not done a medium run he can still only score for 6 races. The spreadsheet has given him fewer points than on line 39 so in effect he has been penalised for doing this extra run.

By counting the 4 pointer and discarding the 11 pointer the runner satisfies rule 5 by completing 2 OR categories but, in this scenario the 4 pointer should be dropped, being the lowest score, not the 11 because the runner can still only score for 6 races and still has the opportunity to satisfy all rules by doing just one more run.

The second one is less complicated and is detailed on the spreadsheet.

Cheers

Hi,

I had not correctly formula-filled past row 37, row 42 is now fixed.

Row 40 I cannot agree the count.
Runner has 6 races, and if the runner scores in m/or then the runner will satisfy the Rules for a seven-races score, therefore all 6 should count.
This is a rules interpretation anomoly, row 10 and row 40 you have applied different interpretations to the rules.
In row 10, if we follow your row 40 suggestion, then the red cell is used in favour of the yellow cell for a score of 102 (not 100 as shown)

In row 40, that the runner has the 4 points would normally introduce that to the runners score, a further m/or still required the lowest available figure (11) to be dropped

Can you confirm the rules and the method of interpretation (yeah, more testing).

Cheers.
---

Originally Posted by Quaisne

I personally think that row 10 should score 102 and row 40, 87. But, I will get clarification from the rule setter on Tuesday when I next see him as to the rule interpretation.

noted, and point out to your Rules person the ludicrous situation where a race that you don't run is worth more than a race that you do run.

Hope you're not too deep in snow.

added, Rules?

1 At least 1 must be category L
2 At least 1 must be category M
3 No more than 5 category S
4 No more than 3 Evening League
5 2 must be OR of different categories

Freely interpreted as:

Column C through G are 5 s/ev races,
of which a maximum of three might count,
providing that the highest, two highest, or three highest are higher than
4, 5 or six other numbers, one or two of which may be lower numbers or zero
but will be deemed to be numbers to be used

Column H though L are 5 s/or rances, 0 to 5 of these races may count,
one of these races, albeit zero (the runner did not participate) may be
deemed to be a race that counts.

Column M through O are three m/or races, 0 to 3 of these races may count,
one of these races, albeit a zero (the runner did not participate) may be
deemed to be a race that counts.
There is no apparent logic as to whether the zero race from s/or or
the zero race from m/or, or both, will be deemed. The zero race from l/or is an added complication.

Column P, the Burn, one of three races required to fulfill Rule 1, however
a runner who runs the Burn may elect to ignore that the race was run and
elect to score a zero from the following two columns, this may achieve higher points.

Columns Q & R, the l/or, one of these may be required (or elected?) to fulfill Rule 5
in which case Rule 1 is then satisfied and the Burn race is not then required solely to satisfy Rule 1.
however, if s/or and m/or have sufficiently high scores then Q & R are not required for Rule 5, then P Q & R must
then be considered for Rule 1, in this case a zero race is not considered to be an option.

In summary,

7 races are scored, two must be from two of s/or m/or or l/or but either or both may be zero (not-attended) races.

s/or & m/or are selected if:
s/or & l/or are selected if:
m/or & l/or are selected if:
5 races remain

if s/or and m/or was selected, then one race from P Q & R is selected and 4 races remain
otherwise 5 races remain

For the remaining races, (the 'pool')
the top 3 s/ev races
if s/or was selected the next 4 highest s/or
if m/or was selected the next 4 highest m/or
if l/or was selected the next 2 highest scores, however,
the selection may have been a zero so just the Burn

Select 5 or 4 races to complete the runners score.

--------------------------------------------------

If your choice of '102 points for Row 10' is correct we will need to determine the blue selections.

added later, this could be easier to score, I am adjusting the Worksheet.

---

Originally Posted by Quaisne

I personally think that row 10 should score 102 and row 40, 87. But, I will get clarification from the rule setter on Tuesday when I next see him as to the rule interpretation.

Hi (again) -

the attached fills your request, the helper columns have now blown out to 58, but I think they are now simple enough to amend in the future.

Working from the 3 blue conditions, I set 3 sets of scores, and simply pick the bst score.

Check a few more scores.

---
amended, rules descriptions improved for documentation purposes.
---

Originally Posted by Quaisne

Hi

The snow was not too bad around here, it was no more than a dusting and was gone the next day. So, we missed the worst of it.

I have passed the query on to the rule maker and he will come back to me on Thursday night with an answer.

In the meantime I will put some test scores in the latest spreadsheet and hopefully post the results of the testing and the answer to the query on Friday.

Ok - plus, in view of the fact that the current version is by far the most stable in formula, and also uses simple-to-understand formulae, then you should persuade him to the thought that a runner should NOT be penalised for entering a race.

The current formula set can be understood by most Excel users.

The Rules are interpreted Rule 5 first, Rule 2 second, Rule 1 third, Rule 4 fourth and Rule 3 last.

Rule 5 produced the 3 (blue) questions, which seemed unanswerable (they really do need someone with a brain to provide a clue)

The three questions (well, 1 question, 3 answers, but 'when' to apply which answer) were raised by Rule 5 which gives that '2 must be OR of different categories' for which there are just 3 possibilities, S/OR with M/OR, S/OR with L/OR or M/OR with L/OR (see un-hide columns for colour code and explanations)

For 2 out of three this also satisfies Rule 2 (one M) and for 2 out of three Rule 1 (one L)
For the other set Rule 2 and Rule 1 are separately applied.

3 'ev' races together with either 4 or 5 S/OR are then used to determine the 4 or 5 highest available to the general 'pool'.
The M/OR category 'leftovers' are pooled together with the Burn race and any L/OR leftovers.

For each 'set' (of three sets) a total points is counted, of course this may include some 'zero' cells as required to fulfill various rules (the 102 points on row 10 etc) and the best of these three scores is taken to be the runners Points score.

Compared to the complexity of the previous version the formulae used here are extremely simple, if somewhat repetative.
Because the 'set' has determined the difficult part of Rule 5 there is little left to test, the 'set' also determines whether there are 4 or 5 'short' races in the pool, and when the Burn race applies.

Between column T and CC there are no tests. for most just one simple =Large(A1:A2,2) type of formula.
The three 'points totals' are fixed range sum's, and the final choice another 'Large'
All too easy.

Good luck with your testing, it is the best offering to date.

---

Originally Posted by Quaisne

Got an e-mail and it is agreed that the way your latest spreadsheet scores the points is the one that will be used. So, I will get some more testing done now.

good to see, and you should also print the previous post to file with the print of the Rules in case you ever get lucky enough to hand this to some other (poor) runner.

btw, is your que-ney sounding name connected with Jersey or France?

---

Originally Posted by Quaisne

Okay thank you Bryan I have been unable to find any scenario that the spreadsheet cannot handle. If I do I will let you know.

the sheet is too simple, and the more simple the less chance of error.

I have copied your post for future reference and will have a good look at how you constructed the spreadsheet.

the sheet turned out to be easy, for each 'set' (blue rule) pick up the 'required' figures, then work out the 'short' pool starting from 'Large,2' where a figure was already used, and from that the remaining 'pool'. The number of 'fixed' figures is known for each set, so the number drawn from the pool is also known. To understand the sheet just work on one set, and once you have worked out a set the other two sets are similar, with just minor variances.

Quainse is Jersey related. I went to school in Jersey and my parents lived a short walk over the common from Quaisne Bay.

ok - Jersey and Guernsey were two places I read about and wanted to visit as a schoolboy, but like trips to the Isle of Man and to Wales never eventuated.

Also in the notes on the spreadsheet you remarked about the comment

""use sum function and turn none counting scores into strings."

Unfortunately I cannot reveal anything exciting for you, that was merely a note about how the problem would be dealt with manually if the spreadsheet could not be automated.

the point there is that a formula cannot make itsself a string depending on the Rules without using circular references, and you don't want to do that.

Once again thank you for all your effort. I knew everything was possible in Excel it was just a case of finding the right man.

the right Rule interpretation was a better find. good luck to yourself and all the Nidd runners.

---
added

ps, be sure to let the person who gave you this task know that 'it's all too easy'.
---