• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

FANTASY F1 - WHAT'S THE BEST SOLUTION/APPROACH ?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / FANTASY F1 - WHAT'S THE BEST SOLUTION/APPROACH ?|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…FANTASY F1 - WHAT'S THE BEST SOLUTI…
sp_PrintTopic sp_TopicIcon
FANTASY F1 - WHAT'S THE BEST SOLUTION/APPROACH ?
Avatar
Nick Hamilton

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 8, 2020
sp_UserOfflineSmall Offline
1
June 1, 2021 - 11:53 pm
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
June 2, 2021 - 11:46 am
sp_Permalink sp_Print

Hi Nick,

I recommend you use Excel Forms to gather the data. Hope that link points you in the right direction. 

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Nick Hamilton

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 8, 2020
sp_UserOfflineSmall Offline
3
June 7, 2021 - 9:06 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
June 8, 2021 - 10:02 am
sp_Permalink sp_Print

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

Avatar
Nick Hamilton

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
December 8, 2020
sp_UserOfflineSmall Offline
5
June 8, 2021 - 5:35 pm
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
June 8, 2021 - 7:07 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard Benson-King
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.