Results list problem

[Davout]

Hi everybody!

In my spare time, I'm doing whatever I can to help out at our local skiing clubs' "youngster department" and since I have a slight movement disability, doing the numbers and results is what I volunteered to do. Now I would desperately want to streamline the excel sheet we have, which really isn't much of a sheet at all as we have to put in every single digit by hand *and* do all the calculations with a calculator. All in all, it makes for lots of errors.

The situation:

We have a number of competitors. The winner will get 20 points ("position points"), the runner up will get 18, the third placed will get 16 and so on down to the 10th placed who will receive 2 points. Below the tenth position, there will be no "position points" awarded. But in addition to this, each competitor will receive 1 point for every competitor beaten ("competitor points"), but only for just that. This means, that if two persons end up at the same position, they will not receive any points for the one at the same position but only for
those below in points.


Example 1: 50 competitors. The winner will receive 20pp + 49cp = 69 points in total. The runner up will receive 18pp + 48cp = 66 points in total. Etc down to the one at position #49 who will receive 1 point in total.


Example 2: 50 competitors here as well, but here the 3 persons at the top are tied. They will each receive 20pp, but will only receive 47cp as they have only beaten 47 competitors each = 67 points in total. The one at 4th place will receive 14pp + 46cp = 60 points in total.


Every competitor have one row each (the usual way, name to the left, club, sex, age etc in the adjacent cells to the right). Of course, I don't know in beforehand who will win and the column with their respective final positions will be completely random. Since there will be quite a few competitors in each race and further, several races in the year, I want to be able to minimize the "human input" to only entering the number of contestants and the final position for each. Earlier we have entered all the numbers by hand and it have always ended up with errors and more than just a bit of hard words, accusations and so on. Which is rather sad since this, like I said, is all done free of charge on our spare time.

The problem:

Whenever two or more competitors end up in the same position, every way of writing this that I have tried so far, fails. The code must take into account that there can be people ending up in the same position all over the place, ie. 1-3, 8-9 or 34-38 or whatever.

I've tried nested IF-statements for the eventuality that two or more persons end up at the same position but with up to 70 competitors it quickly becomes a mess. Surely there must be a better way doing this. If somebody could point me in the right direction, I'd be grateful!

I hope I have explained things clearly!

Thank you in advance, sorry for any typos and bad grammar as english is not my first language.

Best Regards! / Dav

 

[MarcelBeug]

Welcome to the forum Dav.

Thanks for your efforts to clarify the situation.
Some tiny details is missing like where are the results and what do they look like?
Assuming the competitors are skiing downhill as fast as possible, I guess you have time values somewhere and the smallest value is for the winner.
(E.g. for ski jumping you would have points with the highest values for the winner.)

So assuming time values in T2:T51.
Ranking in U2 and copied down: =1+COUNTIF($T$2:$T$51,"<"&T2)
Positition points in V2 and copied down: =MAX(0,22-U2*2)
Competitors points in W2 and copied down: =COUNTIF($T$2:$T$51,">"&T2)
Total points in X2 and copied down: =V2+W2

 

[Peter_SSs]

Welcome to the MrExcel board!

I've used much of Marcel's assumptions and am really just posting to give you a visual idea of the suggestions, though I have modified some a little, and also suggest using Excel native function for ranking values.
Each formula is copied down and the colours were manually added to highlight the tied results.

Excel Workbook
	A	T	U	V	W	X
1	Name	Race time	Position	PP	CP	Total
2	Name 1	33.40	10	2	5	7
3	Name 2	31.80	8	6	7	13
4	Name 3	36.90	15	0	0	0
5	Name 4	30.00	1	20	12	32
6	Name 5	31.40	5	12	9	21
7	Name 6	30.00	1	20	12	32
8	Name 7	35.10	13	0	2	2
9	Name 8	30.70	4	14	11	25
10	Name 9	30.00	1	20	12	32
11	Name 10	34.10	11	0	4	4
12	Name 11	36.30	14	0	1	1
13	Name 12	31.90	9	4	6	10
14	Name 13	31.40	5	12	9	21
15	Name 14	34.40	12	0	3	3
16	Name 15	31.60	7	8	8	16
Skiers

 

[Davout]

Thank you *very* much for your answers, both of you! And wow, you were fast! I've only just got back from work and haven't had time to put it into my excel sheet yet but this certainly looks like it will do the trick. I might try some tinkering to get the whole of it to look like I want it to but that's just a trifle, easily done with some hammerblows to the computer

Again, thank you very much!! / Dav

 

[Davout]

And yes of course, you were both right in assuming time is another column, although we're talking cross country skiing here and not downhill. But since it's about young(ish) kids, we don't want to divide them up with fractions of a second but have thought it wise to satisfy ourselves with seconds, which makes for sometimes a rather crowded podium :D

 

[MarcelBeug]

You're welcome.

 

[Peter_SSs]

You're welcome from me too.
Glad it seems to have given you something that you can progress with.

 

[Davout]

Hello again!

Note to self: Don't fix things, it'll only force you to fix *more* things


When I had finally gotten the above to work - thankyouveryverymuch! - one of the board members of our skiing club immediately suggested that I ought to incorporate whenever there are too few competitors for them to be counted gender wise, ie. when there are less than six boys or girls. Stupid as I am, I didn't think this would be a problem so ****ily I uttered "sure, np!" Yeah right...


In each gender results sheet and for each race, I have one cell where I put in the number of competitors. Then in each genders calculation sheet (I like to keep all calculations etc in one place = less messy) I have tried combining IF and COUNTIF formulas but for a few (read: several...) days now I keep ending up either with wrong numbers, 0 or "FALSE". Yet Excel seem to find nothing wrong with the formula so at least the syntax might be not too far off. This is what I have (I have rewritten it in english for easier understanding):


F7 = Number of competitors
F9 here is only the first of the eventual competitors


=IF('Total Boys'!F7>=6;COUNTIF('Total Boys'!F$9:'Total Boys'!F$69;">"&'Total Boys'!F9);IF('Total Boys'!F7>0<6;COUNTIF('Total Boys'!F$9:'Total Boys'!F$69;">"&'Total Boys'!F9)+COUNTIF('Total Girls'!F$9:'Total Girls'!F$69;">"&'Total Boys'!F9)))


Explaining what I want the above formula to do, it should first look at F7 and check if the number of competitors are 1-5 or 6 or more. If greater than or equal to 6, it should only look at the range F9:F69 in the Boys sheet.


If there are between 1 and 6 competitors, it should check both ranges F9:F69 in each of the two genders columns and return the number of competitors higher placed than the one it checks against. This since there are less than 6 competitors and to get the points results right (when there are less than 6 of any gender the two genders will compete combined and thus, girls can beat boys and vice versa).


In addition to this I have one more - simpler - formula for each competitor, the result of which I will add to the result of this one, to get the final score when running a combined race. "This shouldn't be so hard to achieve" I thought, but I'm staring my eyes out of their sockets and I still can't get it to work. Can you guys spot where I'm going wrong? Or am I perhaps even using wrong formulas?

Best Regards! / Dav

 

[Peter_SSs]

Deleted - incorrect formula

 

[MarcelBeug]

So the formula should always look in the Boys sheet and - only if <6 competitors - also in the Girls sheet.
Then this should do:

=COUNTIF('Total Boys'!F$9:F$69;">"&'Total Boys'!F9)+IF('Total Boys'!F7<6;COUNTIF('Total Girls'!F$9:F$69;">"&'Total Boys'!F9))

Remarks:
1. In your formula, you repeat the worksheet name when refering to a range. Like: 'Total Boys'!F$9:'Total Boys'!F$69, while 'Total Boys'!F$9:F$69 will do.
2. 'Total Boys'!F7>0<6 won't do what you are looking for. It will first resolve 'Total Boys'!F7>0 which is either TRUE or FALSE and then compare that with <6 which will always be TRUE.
3. The general structure of an IF-function is IF(condition, value-if-true, value-if-false). In your formula, you start the value-if-false part with an IF which is not required as there are just 2 situations.
4. You my notice I have no value-if-false in my formula, meaning the IF-function will return FALSE if the number is >= 6. In calculations, FALSE is treated as zero, which is OK in this formula.

Hope this will help you out.