Many years ago I ran a Fantasy F1 League where participants had to predict the qualifying and race results.
Back then I used a simple Excel spreadsheet to record entries and results but this was pretty laborious as I relied on entrants sending me their predictions in paper format so that I could input it.
Excuse what may be a VERY basic question, but is there a way to get them to "log in" to the sheet to record their entries (without seeing the entries of others) or for me to collect the entries electronically and have the data automatically recorded ?
Regards,
Nick
Hi Nick,
I recommend you use Excel Forms to gather the data. Hope that link points you in the right direction.
Mynda
OK... bear with me on this... and apologies for this being REALLY basic...
I am asking people to predict the first 10 qualifying places and the first 5 finishers...
In the previous (paper based) incarnation of the competition points were awarded for having the correct driver in the correct position at qualifying but there were also points given if the driver was predicted on the correct row or the right constructor was predicted in a position - e.g. Lewis HAMILTON was predicted as having Pole Position but was 2nd with his teammate BOTTAS getting pole - no points would have been scored for an incorrect driver but points would have been awarded for having predicted HAMILTON on the front row and a Mercedes driver on pole).
How on earth do I set something up that will reference the result against the prediction and take into account the scenario above ?
Also, if I ma collecting the entries via Forms, how can I automate the scoring process ?
Sorry to be a pain... gotta learn somehow...
Hi Nick,
Looks like you're on the right track. You need to add the additional data for the team name (Mercedes) which you could do with a lookup in columns added to the Entry sheet. Then you can modify your IF formulas to IF(OR... where required.
Hope that helps.
Mynda
Thanks Mynda,
The VLOOKUP works great... now I'm having an issue with calculations for the scores for predicting a driver on the correct row of the grid -
If HAMILTON is predicted on pole but qualifies second points are awarded for having him on the correct row of the grid... I seem to have confused my IF OR formulas... (see the SCORING MATRIX tab on the attached).
There is probably a much easier and logical way of doing this which escapes me.
Many thanks for your assistance with this Excel numpty so far.
Hi Nick,
I think this is what you need:
=IF(OR(ENTRY!$D$2=ENTRY!D5,ENTRY!$D$2=ENTRY!F5,ENTRY!$F$2=ENTRY!D5,ENTRY!$F$2=ENTRY!F5),2,0)
Mynda
