Diego Miranda
New Member
- Joined
- Jan 26, 2017
- Messages
- 1
Hi Mr. Excel forum members, first post here from me.
I need assistance in figuring out if it is possible to create a formula based on a complicated set of instructions.
This is some desk research for an investigation where I’m using the rankings system from the ATP (The professional tennis association)… The following paragraph comes directly from their website:
“The year-end Emirates ATP Rankings is based on calculating, for each player, his total points from the four (4) Grand Slams, the eight (8) mandatory ATP World Tour Masters 1000 tournaments and the Barclays ATP World Tour Finals of the ranking period, and his best six (6) results from all ATP World Tour 500, ATP World Tour 250, ATP Challenger Tour and Futures tournaments. For every Grand Slam or mandatory ATP World Tour Masters 1000 tournament for which a player is not in the main draw, and was not (and, in the case of a Grand Slam, would not have been, had he and all other players entered) a main draw direct acceptance on the original acceptance list, and never became a main draw direct acceptance, the number of his results from all other eligible tournaments in the ranking period, that count for his ranking, is increased by one (1).”
So basically each player can count points from his 18 best tournaments but as long as he plays ANY “Grand Slam” or “ATP World Tour Masters 1000 tournaments” then those results count towards the 18 tournaments, even if they are not his best results. I am having quite a bit of trouble creating a formula that automates this calculation after inputting tournament results by week; I can easily create a formula that counts the best 18 results using this formula “=IFERROR(SUM(LARGE(M7:PD7;{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}))+PH7;(SUM(M7:PD7))+PH7)”, but I can’t figure out how to make one that understand which “type of tournament” the points come from. (The “+PH7” part is because there is a tournament at the end of the year that counts as a 19th countable tournament for those who participate in it)
The structure of the current dataset (rankings file looks like the screenshot below):
So the players are placed vertically in rows and the weekly results are placed horizontally in the columns. Each week contains four cells of information: TOURNAMENT NAME | TOURNAMENT TYPE | RESULT | POINTS, so the spreadsheet extends 52 (# of weeks) * 4 (cells per week).
Do you guys have any clue on how one could create a formula that would count the best 18 results (coming from the cells “POINTS”) but also taking into account the type of tournament??????? :/
So for example, one player has played 23 tournaments and his 17th and 18th best results are 45 and 25 points respectively, but he played in two tournaments categorized as GS2000 (Grand Slam, which are mandatory) and got 10 points for both tournaments then those two results would be included in the calculation of the “best 18 results” rankings formula (16 best results + results from the 2 mandatory tournaments played).
Very much looking forward to your replies or ideas.
Have a nice evening!
I need assistance in figuring out if it is possible to create a formula based on a complicated set of instructions.
This is some desk research for an investigation where I’m using the rankings system from the ATP (The professional tennis association)… The following paragraph comes directly from their website:
“The year-end Emirates ATP Rankings is based on calculating, for each player, his total points from the four (4) Grand Slams, the eight (8) mandatory ATP World Tour Masters 1000 tournaments and the Barclays ATP World Tour Finals of the ranking period, and his best six (6) results from all ATP World Tour 500, ATP World Tour 250, ATP Challenger Tour and Futures tournaments. For every Grand Slam or mandatory ATP World Tour Masters 1000 tournament for which a player is not in the main draw, and was not (and, in the case of a Grand Slam, would not have been, had he and all other players entered) a main draw direct acceptance on the original acceptance list, and never became a main draw direct acceptance, the number of his results from all other eligible tournaments in the ranking period, that count for his ranking, is increased by one (1).”
So basically each player can count points from his 18 best tournaments but as long as he plays ANY “Grand Slam” or “ATP World Tour Masters 1000 tournaments” then those results count towards the 18 tournaments, even if they are not his best results. I am having quite a bit of trouble creating a formula that automates this calculation after inputting tournament results by week; I can easily create a formula that counts the best 18 results using this formula “=IFERROR(SUM(LARGE(M7:PD7;{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}))+PH7;(SUM(M7:PD7))+PH7)”, but I can’t figure out how to make one that understand which “type of tournament” the points come from. (The “+PH7” part is because there is a tournament at the end of the year that counts as a 19th countable tournament for those who participate in it)
The structure of the current dataset (rankings file looks like the screenshot below):
So the players are placed vertically in rows and the weekly results are placed horizontally in the columns. Each week contains four cells of information: TOURNAMENT NAME | TOURNAMENT TYPE | RESULT | POINTS, so the spreadsheet extends 52 (# of weeks) * 4 (cells per week).
Do you guys have any clue on how one could create a formula that would count the best 18 results (coming from the cells “POINTS”) but also taking into account the type of tournament??????? :/
So for example, one player has played 23 tournaments and his 17th and 18th best results are 45 and 25 points respectively, but he played in two tournaments categorized as GS2000 (Grand Slam, which are mandatory) and got 10 points for both tournaments then those two results would be included in the calculation of the “best 18 results” rankings formula (16 best results + results from the 2 mandatory tournaments played).
Very much looking forward to your replies or ideas.
Have a nice evening!
