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
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
