Declamatory
Active Member
- Joined
- Nov 6, 2014
- Messages
- 319
Hi Guys,
I have a spreadsheet that holds information on results of rounds of golf. Each round has a separate tab with the the players in column A then the scores for each hole in columns B, C D etc.
The competition is an ecclectic where each player plays 4 rounds. The lowest score on each hole is the one that the player would use when calculating their best round. E.g. if the player had an eagle on hole 4 when playing round 6 that would be the score they would use for hole 4 when calculating their best overall round. The best score for hole 5 might be from round 2.
The competition takes place over 8 weeks. A player doesn't have to play all 8 rounds. They just need to play 4. They can choose to miss some weeks and only play 4 rounds. A player can play all 8 rounds but only the first four rounds would count when calculating their best round. The last four rounds would be ignored.
The data for each round is imported from a program where the players input their scores. The data only shows players who played each so some players would not appear on every tab. Only the rounds where they played.
The workbook also has a 'Master' tab that shows the accumulative best round for each player using the method above. I thought of creating an array for each round and in the master tab use MIN & VLOOKUPS to bring through the score on each round using IFERROR if the person didn't appear in one of the tabs.
The formula I tried for each hole was
That doesn't work.
The problems:
1) How do I calculate a players lowest score on each hole ignoring rounds where people haven't played.
2) How do I get the formula to only count the first four rounds each competitor plays and ignore the rounds after the first four where the competitor played but the score should be ignored.
3) If a player doesn't play the first week they wouldn't appear in the master tab. I need a way to identify new competitors who have entered the competition after the first week (some players may make up their 4 rounds by playing the last four weeks)
4) I would like to show the scores after each week in the master tab. This means that some of the data for some rounds won't appear in the spreadsheet until the round has been played and the data imported into the workbook.
I hope I've explained myself Ok but if you need further clarification please let me know.
Thanks
Dec
I have a spreadsheet that holds information on results of rounds of golf. Each round has a separate tab with the the players in column A then the scores for each hole in columns B, C D etc.
The competition is an ecclectic where each player plays 4 rounds. The lowest score on each hole is the one that the player would use when calculating their best round. E.g. if the player had an eagle on hole 4 when playing round 6 that would be the score they would use for hole 4 when calculating their best overall round. The best score for hole 5 might be from round 2.
The competition takes place over 8 weeks. A player doesn't have to play all 8 rounds. They just need to play 4. They can choose to miss some weeks and only play 4 rounds. A player can play all 8 rounds but only the first four rounds would count when calculating their best round. The last four rounds would be ignored.
The data for each round is imported from a program where the players input their scores. The data only shows players who played each so some players would not appear on every tab. Only the rounds where they played.
The workbook also has a 'Master' tab that shows the accumulative best round for each player using the method above. I thought of creating an array for each round and in the master tab use MIN & VLOOKUPS to bring through the score on each round using IFERROR if the person didn't appear in one of the tabs.
The formula I tried for each hole was
Code:
=MIN(IFERROR(VLOOKUP($A9,round1,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round2,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round3,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round4,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round5,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round6,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round7,6,FALSE),"DNP"),IFERROR(VLOOKUP($A9,round8,6,FALSE),"DNP"))That doesn't work.
The problems:
1) How do I calculate a players lowest score on each hole ignoring rounds where people haven't played.
2) How do I get the formula to only count the first four rounds each competitor plays and ignore the rounds after the first four where the competitor played but the score should be ignored.
3) If a player doesn't play the first week they wouldn't appear in the master tab. I need a way to identify new competitors who have entered the competition after the first week (some players may make up their 4 rounds by playing the last four weeks)
4) I would like to show the scores after each week in the master tab. This means that some of the data for some rounds won't appear in the spreadsheet until the round has been played and the data imported into the workbook.
I hope I've explained myself Ok but if you need further clarification please let me know.
Thanks
Dec
Last edited:
