Triathlete Club Needs Help!

[James Musgrave]

Okay, I'm not a total illiterate when it comes to Excel, and have used it to
put together some basic spreadsheets for my business, but I started mucking
around with a results sheet for my triathlon club when I realised what I
really need to do is WAY beyond my meagre skills and capabilities. I just
thought I'd drop a line here and ask if this is something that Excel can
even do, or if it would be more in the realms of Access or even a
custom-written program.

This is what we need.

Every month when we race, we simply need to input each competitor's name and
time into a sheet (yep, I can do that)

There are Five Grades:

A-Grade Men
A-Grade Women
B-Grade Men
B-Grade Women
C-Grade Juniors

Members are awarded each points for their finishing time.
a.. Competitor A wins the grade with a time of 50mins - 1000pts
a.. Competitor B placed 2nd in 55 min - 90.9% of winning time - 909pts
a.. Competitor C placed 3rd in 60 min - 83.3% of winning time 833pts

We need to have each competitor's time and points put onto a cumulative
pointsheet which would read off each month's sheet. Not every racer
competes each month

We would then need the spreadsheet to rank the competitors by cumulative
points in the various grades, which would obviously be updated each race

At the end of the year, we would then need the spreadsheet to take each
competitor's top five points from the seven races of the season, and add
them up - ranking them again by highest to lowest in the five grades.

Each competitor needs to marshal a race, so if they don't their points are
still accummulated but they don't recieve a Qualification. If we could only
have the members who qualified first as a group in each grade, followed by
the members who didn't marshall even if their scores are higher than the
members who did. I don't know - would a radio button do here?


So how about it? Is this even within the realms of Excel or do I need to
see if a kind-hearted programmer can do something?


James Musgrave
Triathlon-Nerd

 

[anon]

Hi James,

This is complex...but not impossible. Your solution depends on whether
you want to spend a little time becoming aquainted with Excel code and
therefore creating this yourself with help from people on the forum,
or whether you don't have the time spare in which case you should look
for a kind hearted developer!

My initial answers/questions would be;

What is the relationship between grades and points? (I'm guessing
none, each competitor has a grade to start and is ranked in points
against other competitors in their grade)
Are there set 'rules' for the points? Eg. in your example what would
competitor B's points be if he had finished second in 80% of the
winning time?
Do competitors have to marshall at every race or just once a year?

Simply put I think put you'd need;

Sheet1 - input sheet - competitor names down the side, race numbers(or
month of race) across the top and you'd input the time for the race
into the grid.

Sheet 2 - points sheet - A matching grid to sheet 1, however would be
points shown (instead of race times), and no inputting required.

Sheet 3 - Year End Sheet - The year end grid would be created in code
to find the top 5 points for each competitor

Regards the marshalling, this is simply a yes/no box for each
competitor and this can affect the ranking easily enough.


You need to start by creating sheets 1 and 2, and then putting the
code into sheet 2 to fill it. You could run the code from a command
button on sheet 1 called 'Update Points'. The code would;

Copy the competitor names from sheet 1 into sheet 2
Copy the race numbers from sheet 1 into sheet 2
Calculate the points for each competitor depending on their time in
sheet 1 and put it into sheet 2
Sort the competitors by grade, then marshalling, then by points

Then you can look at sheet 3 - the end of year. It's difficult to give
code without a sheet structure however post back if you're going to
have a go and i'll try and help.

 

[James Musgrave]

Hi anon, how can I chat to you more about this? You can catch me at
nerdwearclothing (at) hotmail.com

 

[anon]

Hi, i've dropped you an e-mail from another e-mail address so please
reply to me there