Leaderboard/Ranking Question

[kirbster123]

Hi,

I've tried to search on the forums and google, but I'm a bit new to Excel and have a bit of trouble understanding some of the functions. We have a league that runs for ten weeks, each week with some amount of new/different players. We would like to make a leaderboard that automatically takes the new AND existing names and puts into a one sheet leaderboard. The league runs on every Thursday and Friday of the week, we would like to take the best score of either day to count towards the leaderboard. Is this possible and if not, is there a way to set something similar with minimal manual input?

Thanks

[Pete_UK]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

[kirbster123]

Sorry for the late reply - this is an example of some of the data we need used. Two days a week (best result from either day) for ten weeks are used for the final leader board, with the last two weeks being dropped. We've used the following point system to determine ranking:

WEEKLY LEADERBOARD POINTS
12 points = 6 points
10 points = 5 points
9 points = 4 points
7 points = 3 points
6 points = 2 points
all else = 1 point

Hope that makes sense and thanks for the help!

[GeoffW283]

OK, here's one attempt.

First I have chosen to combine your daily tabs into a single worksheet called "Data" with an additional "Date" column to still retain the chronology. It's just easier this way to manipulate the data.

The Light blue shaded columns in the "Data" worksheet are where you enter new data exactly as you have already done for the first two weeks.

The remaining columns on the "Data" worksheet are calculated. "Week points" implements your requirement that points for the week are the maximum of the Thursday and Friday results. I think I have the math right but you should check.

As I understand your requirements "Leaderboard" points are different to "Weekly" points. The "PointMapping" worksheet specifies this mapping. The "Leaderboard points" column of the "Data" worksheet calculates Leadership points from the "Week" points.

Finally the Leaderboard worksheet is a pivot table giving you something very close to your template. It is sorted by Leaderboard points highest to lowest. It has columns for each of the first 2 weeks showing Leaderboard points per week. The pivot table should automatically extend to show additional week columns when new data is added.
I didn't know what you meant by "with the last two weeks being dropped", so that is not implemented.

Hopefully this provides one idea outline for how to approach this task that you will find useful.

The attached spreadsheet implements the above.

[kirbster123]

Thank you so much for the help! Looks great! What I meant to say is that each player has there lowest points from 2 weeks dropped from the final standings. So only the best 8 out of 10 weeks would count. How would I be able to implement this?

Thanks

[GeoffW283]

I have uploaded a revA including the following changes:

1) I resolved a calculation error - Leaderboard points were erroneously being doubled.

2) I added a "Best 8" column to the LeaderBoard worksheet per your clarification.

3) I added rows 119-129 of the Data worksheet for testing purposes. Delete/add or modify these to do your own testing.

There may be a residual calculation error depending on your answer: Can you confirm that a player who gets zero points in a given week that they still get 1 "Leadership" point for that week?

Finally note that if you play with the values on the Data sheet you will need to right-click somewhere on the Leaderboard pivot table and select "Refresh" to get it to show the latest data.

[GeoffW283]

Updated attachment --> RevB

- Fixed the residual calculation error noted in the previous post. It should work correctly regardless of whether or not a player who gets zero points in a given week still gets a "Leaderboard" point. Still need that answer though to make sure the PointMapping table is correct.

[kirbster123]

I added the remaining data and tried to plug it in, but couldn't get it to work? And yes, a person who gets 0 points in a week still gets one point. Thanks so much for the help again, you're a lifesafer. I have to learn more excel as I'm awful with it!

[GeoffW283]

I noticed there is a block of cells A110:A145 misformatted as numbers rather than dates but that does not really affect anything.

You have overridden the formulas in E117:E388 with a hand calculated week number. There's no need to do this as this field is calculated from the date you provide in column-A. Similarly you have overridden the formula in G117:G388 which is set to the "other" day of the same week. It looks like you have manually filled in what the formula would have calculated, there was just no need.

So in summary when adding new data you need only:
a) Provide new row data for columns A:D of the "Data" worksheet.
b) Select formula columns E through K and copy down through the new rows you just added.

I have uploaded a version of your spreadsheet with the formulas restored.

I don't think anything you did altered the Leaderboard calculations so when you say "I couldn't get it to work" can you give me a few specific examples of where you think it's going wrong? It's working as I intended but that may not be what you want

Thanks

[kirbster123]

SOLVED - ignore. Thanks!

[GeoffW283]

I believe only you can mark the thread as solved: To do this select "Thread Tools" from the menu at the top of this thread and mark this thread as SOLVED.

Out of curiosity, did this do everything you wanted?

Geoff