Stats (points) tracking for a disc golf league

[sennsm]

Hello, i am not at all skilled in excel. I am a graphic designer. those are worlds apart. LOL.

I run a disc golf league. Scoring is exactly like regular golf.

We are using a points series to crown a champion for the season. I need a spreadsheet that can track a players points based on his finishing position in each tournament. our events have abut 30 players average but 50+ unique members in the club. so if i had a sheet for each tournament that i could input results and then a leaderboard sheet where it calculated the players points and sorted them in order. i found a thread from 5 yrs ago where someone made one for another user. it was for golf, and might work but i cant figure out how to customize it with it going haywire. anyone willing to help? thank you in advance.

here is that thread https://www.excelforum.com/excel-gen...petitions.html

[BlindAlley]

I can help you with this.

We will need to post updates and formula examples as we go along so we can help anyone else interested in doing this as per the forum rules.

So the 1st thing is an index tab with 50 names or so that will collect the data from the tournament tabs and create the ranking and league tables, the 2nd thing is how many tournaments are expected in the season ?

[sennsm]

Awesome. Thanks. It will probably be 6-8 events. But is there a way to leave it open for more? And it is possible we have more than 50 players. Would I just be able to add them throughout the year? On the tournament sheet I would need a column for the players score and pts earned. Question... is there a way that the player can be assigned points based on his finishing position in the tournament or would I need to input that along with his score manually? The points system is like this... if there are 20 players then 1st gets 20 pts, 2nd gets 19, 3rd gets 18 and so on. With last getting 1pt. Not all tournaments will have the same number of players. But if that has to be entered manually then that it Ok. Thank you for you help.

[sennsm]

I can help you with this.

We will need to post updates and formula examples as we go along so we can help anyone else interested in doing this as per the forum rules.

So the 1st thing is an index tab with 50 names or so that will collect the data from the tournament tabs and create the ranking and league tables, the 2nd thing is how many tournaments are expected in the season ?

Awesome. Thanks. It will probably be 6-8 events. But is there a way to leave it open for more? And it is possible we have more than 50 players. Would I just be able to add them throughout the year? On the tournament sheet I would need a column for the players score and pts earned. Question... is there a way that the player can be assigned points based on his finishing position in the tournament or would I need to input that along with his score manually? The points system is like this... if there are 20 players then 1st gets 20 pts, 2nd gets 19, 3rd gets 18 and so on. With last getting 1pt. Not all tournaments will have the same number of players. But if that has to be entered manually then that it Ok. Thank you for you help.

[BlindAlley]

So the 1st thing we are going to do is set up a dummy league & Tournament. We can start with this example and move along adding and changing formulas and formatting to meet your criteria etc.

The idea is to Automate the worksheet to count all tournament scores, and then start creating a ranking system and a league table.

You can ADD as many players as you like, and ADD as many tournaments as you need later.

Feel free to ask questions and make suggestions as we go along.

[BlindAlley]

This is what we have so far:

I have created a starting point and a dummy tournament League.

Formulas Used

Rank Formula
=RANK(F10,$F$10:$F$25,0)+COUNTIF(F10:F$10,F10)-1 Needs to be scrolled down through the Scoring Range
RANK looks through all of the scores from the Tournament tab and assigns a rank to that number compared to the other players scores.

Offset Formula
=OFFSET(E$10,MATCH(SMALL($G$10:$G$25,ROW()-ROW(E$10)+1),$G$10:$G$25,0)-1,0) Needs to be scrolled down through the Scoring Range
OFFSET looks at all of the scores from the Ranking column and assigns the players names in RANK Order.

IFERROR - INDEX - MATCH Formula
=IFERROR(INDEX($F$10:$F$25,MATCH(I10,$E$10:$E$25,0)),"")
INDEX looks through the Score column and matches the RANKED NAMES to the Players actual game score.
the IFERROR checks the values in the range for errors and returns the value or a blank if there is an error

To create a New Tournament Tab
Right click on the previous tab (6) name and select 'Move or Copy' then select 'Info' then check create a Copy
a new sheet will then be created at the end of the Tourney tabs. Change the data to reflect the New Tournament
To rename a Tab
Right click on the previous tab name and select 'Rename'

[sennsm]

looking over it now.

[sennsm]

i would only need a total score and points for each tournament. not multiple rounds. you could leave them in case we have a multiple round event. and right now it is ordering players with the highest total in first place. in disc golf like in golf, it is the player with the lowest score that wins. would need to change that. and there is not a points column. this is going to be a cumulative total over all tournaments to crown a champion. not based off their scores, but off of their total point they accumulate in each event. so we need a leaderboard that would sort players by the highest total.

[BlindAlley]

Easy there soldier that was just a five minute mock up

I've already switched that to rank the lower scores and started on setting up the league data.

I am taking the wife out for dinner shortly and will take a look at the sheet again either tonight or tomorrow morning.

[sennsm]

Easy there soldier that was just a five minute mock up

I've already switched that to rank the lower scores and started on setting up the league data.

I am taking the wife out for dinner shortly and will take a look at the sheet again either tonight or tomorrow morning.

Awesome. No worries. Thanks again for taking the time to help out. I really appreciate it. Just let me know when you have another version for me.

[BlindAlley]

OK, I am back working on this again.

Questions:

Would everybody play in each tournament ?

What is an average low score and an average high score ?

[sennsm]

OK, I am back working on this again.

Questions:

Would everybody play in each tournament ?

What is an average low score and an average high score ?

no. not everyone will play in all.
low - 56
high - 75

[BlindAlley]

K Thanks,

I am working on the Tournament Tab at the moment, setting up the total score and then sorting them into low to high scores.

[sennsm]

K Thanks,

I am working on the Tournament Tab at the moment, setting up the total score and then sorting them into low to high scores.

thx. and on the points. you can do it 1 of 2 ways. whichever is easiest for you. either a point per player they beat or the way i mentioned earlier.

[BlindAlley]

Hi again,

I have created tabs for 8 Tournaments and put in dummy names, scores & Points.

This data is then transferred to the League Table Tab using the INDEX MATCH formulas. Totals have been counted for the season.

Using this dummy information giving 2 points for a match win and 1 point for the loss we end up with a lot of end of season tied scores. Before I go further take a look and see where the worksheet is at and let me know what you think.

[sennsm]

the player would not be playing vs one opponent. see my attached image for reference. i basically need a tab for each tournament with score and points then it link over to the league standings page that calculates and sorts the players in order of most pts to least pts. you earn a point for each player you beat or tie

IMG_1547.JPG

[BlindAlley]

"you earn a point for each player you beat or tie"

Ok no problem - easy fix, it was my misunderstanding of this comment earlier in the thread.

[BlindAlley]

Getting Close ?

[sennsm]

Getting Close ?

ok. i think i am figuring it out. yes, its close. points are still being awarded in the opposite direction. low score get the most points. and in the next version, can you clear all the sample data so i can give it a run through?

[BlindAlley]

Ok, I have switched the points to Low Score, High Score.

[BlindAlley]

and one with blank data

[sennsm]

and one with blank data

ok. so giving it a run through it looks like it is making me enter scores in all tournaments before it will calculate the season points (League Table - Column V) need that to calculate after one tourn and then after 2 and so on.

and it isnt populating the points rank at all (League Table - Column AH, AI, AH)

Dont need the "Date Joined" unless you need it.

[BlindAlley]

Fixes made.

The table will show blank for all Tournaments until they have been played.

Make sure that you click on 'File, 'Options, 'Advanced then scroll down and UN CHECK the box next to "Show a Zero in cells that have a zero value"

[sennsm]

Fixes made.

The table will show blank for all Tournaments until they have been played.

Make sure that you click on 'File, 'Options, 'Advanced then scroll down and UN CHECK the box next to "Show a Zero in cells that have a zero value"

I didn’t get an attachment

[sennsm]

never mind got it

[sennsm]

everything looks good. only thing is the tournament pages. is it possible to sort by score so i can see the order of finish? when i sort it and expand cells it moves the tournament heading also. if i dont expand cells, well it obviously doesnt work.
Screen Shot 2018-01-08 at 7.38.56 PM.png

[BlindAlley]

See tab 'Tournament 1' for comments on how to SORT the points data into High Low Scores and Clear the data range.

Other changes have been made to the League Table tab so save this version over the latest version you have.

Some last changes may have to be made depending on how you enter the players and scores into each tourny btw.

I have to get up early for work tomorrow so I will check the thread for any new suggestions when I get home in the afternoon.

[sennsm]

See tab 'Tournament 1' for comments on how to SORT the points data into High Low Scores and Clear the data range.

Other changes have been made to the League Table tab so save this version over the latest version you have.

Some last changes may have to be made depending on how you enter the players and scores into each tourny btw.

I have to get up early for work tomorrow so I will check the thread for any new suggestions when I get home in the afternoon.

Looks like this is it. WOW! thanks a ton. i have made a copy. in case something goes wrong. LOL. cant thank you enough. looks great.

[BlindAlley]

Looks like this is it. WOW! thanks a ton. i have made a copy. in case something goes wrong. LOL. cant thank you enough. looks great.

Thanks for your comments, glad you like it.

Let me know how the scoring works out in an actual tournament for you.

[BlindAlley]

Hi again,

After running a couple of possible real life scenarios I have made some final adjustments to the spreadsheet.

1) If a number less than your total membership played in any tournament then the formula would only assign points
based on the number of members that actually played in that particular tournament. So in effect if 47 played and not 50
only 47 points would be assigned to the low score, but in the next match if all 50 played then the low score would be assigned
50 points. Obviously this penalizes matches played with the fewer members

I have changed the system to count how many members played and still assign the maximum points available and so on in descending order.

2) Simply entering a ZERO next to a member who doesn't play or leaving the cells blank doesn't work because a zero score would become the
minimum score and be assigned the 50 points and also change the league table.

Entering DNP next to the members who do not play solves quite a few problems and still allows the MIN MAX formulas to populate the low and
high scoring lists.

3) I have updated the information tab to give a clearer understanding of how the spreadsheet works.

[outofthedark]

Hi. I'm trying to download this excel sheet. I love the work you guys put into this spreadsheet and I think it will work for me really well as well.
It however says that I do not have privileges to do this action. Is there something that I need to do before I am allowed to download?
Is it possible for you to send the file to me another way?

THANKS!