Mark Gravo
New Member
So I'm struggling to adequately describe in words in the title what I need to achieve.
I have a results spreadsheet for our sailing races, and one format of our racing is the Mark Foy Handicap Start. My main spreadsheet has a worksheet for each race, a list of the boat names alphabetically, and calculations for elapsed time, the handicap for each boat and calculations for handicap times, places and adjustments.
Typical Sailing races - each boat has a performance "handicap" in minutes. When all boats start at the same time, the handicap results are the elapsed race time less their handicap. The "fastest" boat has zero handicap. For handicap starts, the boats start a number of minutes before the race start time. This is in the table, based on the schedule start time of 13:00 (1 p.m.). Note the table is in boat name alphabetical order.
Handicap times are adjusted based on the handicap results each race, so the "Current Handicap" (col "B") will change and hence the starting order and start times for each boat will change each time such a race of this format is held.
The start procedure has a warning signal 5 minutes before the first boat, then a preparatory signal 4 minutes before the first boat. For each boat, a board showing their handicap time is raised 1 minute before their start and lowered at their start time.
To make it easier for the race committee, I want to have a set of tables with "Alternate Sequence Times" that lists the various signals and their times, and the boat names and their start times, based on a set of alternate race start times in 5 minute intervals. The bit I need help with is the variability of the order of the boats and the differences in handicap times.
The table with red border is a manual representation of what I'm looking for, based on the list of skiffs (not likely to change) and the current handicaps shown. The only thing that might change is if a skiff can't race and I would put "DNC" in column D of the table (Did Not Compete). That would need to the "code" to not include that boat in the list or calculations. If there's a solution to do all of this then I would just replicate for a number of alternate start times. My preference is not to use a macro or VBA if at all possible.
The main file is already in OneDrive so it was easier for me to just strip down a copy and share it.
H10s Race Results 2022-23 Questions.xlsx
I have a results spreadsheet for our sailing races, and one format of our racing is the Mark Foy Handicap Start. My main spreadsheet has a worksheet for each race, a list of the boat names alphabetically, and calculations for elapsed time, the handicap for each boat and calculations for handicap times, places and adjustments.
Typical Sailing races - each boat has a performance "handicap" in minutes. When all boats start at the same time, the handicap results are the elapsed race time less their handicap. The "fastest" boat has zero handicap. For handicap starts, the boats start a number of minutes before the race start time. This is in the table, based on the schedule start time of 13:00 (1 p.m.). Note the table is in boat name alphabetical order.
Handicap times are adjusted based on the handicap results each race, so the "Current Handicap" (col "B") will change and hence the starting order and start times for each boat will change each time such a race of this format is held.
The start procedure has a warning signal 5 minutes before the first boat, then a preparatory signal 4 minutes before the first boat. For each boat, a board showing their handicap time is raised 1 minute before their start and lowered at their start time.
To make it easier for the race committee, I want to have a set of tables with "Alternate Sequence Times" that lists the various signals and their times, and the boat names and their start times, based on a set of alternate race start times in 5 minute intervals. The bit I need help with is the variability of the order of the boats and the differences in handicap times.
The table with red border is a manual representation of what I'm looking for, based on the list of skiffs (not likely to change) and the current handicaps shown. The only thing that might change is if a skiff can't race and I would put "DNC" in column D of the table (Did Not Compete). That would need to the "code" to not include that boat in the list or calculations. If there's a solution to do all of this then I would just replicate for a number of alternate start times. My preference is not to use a macro or VBA if at all possible.
The main file is already in OneDrive so it was easier for me to just strip down a copy and share it.
H10s Race Results 2022-23 Questions.xlsx