Home

Hello,

Last year I started a fun little side competition during the grand tours, the 'pick three' competition which involved picking three riders from the home nation for a limited budget with the highest scoring team declared the winner.

There was interest in opening this competition up to include more tours such as the Tours of Britain, California, Down Under etc.  I'm happy for people to run this themselves as long as nobody goes overboard (I'm looking at you enrique).

Anyway, my spreadsheets from last year can be found here:

https://www.dropbox.com/sh/b4mptr1pp572xq1/n486LoprFS

The spreadsheets for the Tour of Britain and Vuelta will likely be the best to base any future game on as they are the latest iterations.

Basically you collate a list of riders from the forum, I did this in a 'player - rider 1, rider 2, rider 3' format then dump that into the 'Teams' tab, column A.  The spreadsheet will then separate out your riders.

Scoring wise, taking the Spaniards spreadsheet as an example, I prepared scores on each rest day copying the list of riders from the fantasy game and pasting them into the 'Rest Day X' tab.  You then update the score formula in the 'Teams' tab to point to the correct scores.  For example, look at cell D2 which has a formula =IFERROR(OFFSET(Final!$H$1,MATCH(C2,Final!$C$2:$C$380,0),0),0), that is pointing at the Final scores, changing Final to 'Rest Day 1' would revert back to the first Rest Day scores etc.

My rules for last year were pick three home nation riders for 50 credits, only riders who finish the race score points, highest total wins.  The reason only riders who finish the race were scored is because once a rider drops out they are removed from the roster in the game and are no longer included in the 'riders' list.

Some of the things to watch out for include poor spellings, complicated rider names that don't match the game (Costa/Faria Da Costa etc) and multiple surnames (Sanchez, Sanchez and Sanchez, lots of Rodriguez's and the Herrada's).

Any questions or problems, stick them in here and I'll try and help.

5 comments

Avatar
enrique [2383 posts] 3 years ago
0 likes

Wow. I think I got it. I'm impressed. Nice. I've never used some of the functions you use. Let me ask you a couple of questions. I'm looking at the Spaniards.xslx spreadsheet.

Ok, so you're not storing individual rider stage scores anywhere, so are you adding their individual stage scores on another spreadsheet, or are they somewhere in the spreadsheet I haven't seen? You go straight to 'Rest Day 1' and Rest Day 2''and enter their total scores for the stages before each rest day, right?

I've never used the OFFSET function, can you explain what (OFFSET(Final!$H$1,MATCH(G11,Final!$C$2:$C$380,0),0),0) does? I looked at Excel help but I'm struggling to get it... Thanks!  1

Avatar
drheaton [3318 posts] 3 years ago
0 likes

That's right, individual stage scores are not stored anywhere, I took the running total from the /riders page, that might have to change this year depending on how the game looks. Taking a running total makes it nice and easy to just run the scores once for a race like the Tour of Britain.

Offset is a function which takes a reference point, in this case cell H1 in the 'Final' tab and based on the results of the match function gives you a cell. In this case you're matching whatever is in G11 against a cell in the C column of the 'Final' tab. The match function tells you where the contents of G11 can be found in the C column of Final, uses that as a reference point and pulls put the contents of the H column on that row.

Basically, you match where the name of the rider is in Final and pull through their score value.

Make sense?

Avatar
enrique [2383 posts] 3 years ago
0 likes
enrique wrote:

...I've never used the OFFSET function, can you explain what (OFFSET(Final!$H$1,MATCH(G11,Final!$C$2:$C$380,0),0),0) does? I looked at Excel help but I'm struggling to get it... Thanks!  1

Man, this is an awesome function! Thanks, dr!  1

Avatar
enrique [2383 posts] 3 years ago
0 likes
enrique wrote:

...I've never used the OFFSET function, can you explain what [it] does?... Thanks!  1

Wow, I'm still stumbling with it, but I think I'm getting the hang of it, pretty cool! Thanks, dr!  1 Did you come up with that function nesting yourself? I wish I'd seen it earlier!  1

Avatar
drheaton [3318 posts] 3 years ago
0 likes

Offset matches are fairly common when you get into heavy excel based data analysis, it's a clever way of using two functions together to get a specific result but not something I came up with.