Unsure how to approach my problem, need help with making a league excel sheet

[DrExcel2035]

Hey Guys,

Thank you for reading my question(plea for help), I really appreciate your help (I'm on my knees, begging)

I am making a bowling league excel sheet, I will have 10 - 15 teams.
Each team will start of with a rating.
- There may be no games on a particular day, or more than 1 game.
- The same team will never have more than 1 game per day.
The new rating after the game will be determined by the result of the game, and difference of rating between the games.
Total points will always be the same i.e. If one team loses 20 points, another team will gain those 20 points.

Question:
What s the easiest way to do this?

What I have so far:
Calculate the rating manually insert them under a new heading with a date, and use that to make a graph. I expect to keep this going over the next few years.


All help/comments are appreciated.

 

[Steve=True]

Hi DrExcel2035,

I think we would need more information on the data.

I.e. would you just record the team score or each individual score?
How is the rating calculated?

I also suggest that you check out this post as it is always best to use tables for all your data and then make pivot tables from that data:

think-like-a-database-designer-before-creating-an-excel-dashboard-chart

I imagine that you would want to do record each rating, the date, the team name, have a formula calculate the Month and another for the year. Just my thoughts from left field without seeing the data.

Steve=True

 

[DrExcel2035]

Hi DrExcel2035,

I think we would need more information on the data.

I.e. would you just record the team score or each individual score?
How is the rating calculated?

Steve=True

Thank you Steve=True,

I appreciate you input.

So here is what I plan to do.

For simplicity's sake I will have 5 teams: Pin Pals, Biltmore Bowlers, Strike Squad, Turkish Turkey, Hungry Hambones.

They all start at 0. On Jan 1, 2016 PP beat BB, on Jan 10 PP beat SS, and TT beat HH, Jan 11 PP beat HH, and SS beat TT, on jan 24 SS beat PP

Team 12/31 1/1 1/10 1/11 1/24 End of month rating Rank
BB 0 -10 -10 Fourth
HH 0 -10 -15 -15 Fifth
PP 0 10 18 23 10 10 Second
SS 0 -8 5 18 18 First
TT 0 10 -3 -3 Third

Here is how it worked:
Jan 1: PP beat BB, because they were equal grounds PP gained 10 pts, and BB lost 10 points, hence ending with 10 and -10, respectively
Jan 10: PP beat SS, and TT beat HH
- TT beat HH: ditto, since both had same rating 10 points exchanged
- PP beat SS: since the difference in their rating was 10 points (PP = 10, SS=0), and the stronger team won, only 8 points exchanged, PP now at 18, and SS -8
Jan 11: PP beat HH, and SS beat TT
- PP beat HH: since the difference was 28 points, and stronger team won, only 5 points exchanged, ending with PP and 23, and HH -15
- SS beat TT: Since the weaker team won in an 18 point difference match they were reward 10+3(bonus for beating a stronger team), so 13 points were exchanged
Jan 24: SS beat PP: another 18 point difference and weaker team won, 13 points exchanged.

The sum of ratings of all teams will always remain zero. Since one teams's loss is another team's gain.

What I want to do put in the formula and have the computer take away the points and keep a most current total. so i can graph team's progress over time.


Again, I appreciate all your help.
Hope my illustrations helped with my what I really am looking for? Is there a way to automate this, or will I have to do this manually every time


Dr. Excel from year of graduation: 2035

 

[DrExcel2035]

Chart with proper spacing


Team==12/31===1/1=====1/10====1/11=====1/24======End of month rating=======Rank
BB=====0====-10 =================================-10============Fourth
HH=====0===========-10=====-15===================-15============Fifth
PP=====0=====10=====18======23======10============10============Second
SS=====0===========-8=======5======18============18============ First
TT=====0============10======-3===================-3============Third


hopefully this is more readable, since I replaced spaces with '=' signs

 

[Steve=True]

Thanks for the info DrExcel2035.

So not sure what part you need help charting.

Do you need help with the formulas or the graph?

Here is a sample chart, pretty easy except that you will need to force the horizontal axis to Text instead of Date.

Hope this helps.

Steve

 

[DrExcel2035]

Thanks for the info DrExcel2035.

So not sure what part you need help charting.

Do you need help with the formulas or the graph?

Here is a sample chart, pretty easy except that you will need to force the horizontal axis to Text instead of Date.

Hope this helps.

Steve

Thanks again.

So my questions is that I have to manually calculate the new rating.
Is there a way I can just indicate who beat whom, and have excel calculate their new rating. Because my concern is that I f I make an error in week 2, and discover it in week 15, I will have go back to week 2 and recalculate all the ratings, since new rating depends on previous ratings.

Is that possible in Excel?

Thanks Again

Dr. Excel, class of 2035.

 

[Steve=True]

Sure, anything can be done in Excel

We just need to clarify your formula rules as it is not entirely clear in your description above.

Is this right???

1) If Teams with equal points then Winner gets +10 and Loser gets -10

2) If Teams are Not equal in points then
............if Winning team is the team with Greater Points then they receive +8 points and losing team gets -8 points
............if Winning Team is the team with the lesser points then they receive +11 points and losing team gets -11 points

3) hmmm, as I reread them, they don't make sense to me.

How do you calculate each scenario?

Steve=True

 

[DrExcel2035]

Sure, anything can be done in Excel

We just need to clarify your formula rules as it is not entirely clear in your description above.

Is this right???

1) If Teams with equal points then Winner gets +10 and Loser gets -10

2) If Teams are Not equal in points then
............if Winning team is the team with Greater Points then they receive +8 points and losing team gets -8 points
............if Winning Team is the team with the lesser points then they receive +11 points and losing team gets -11 points

3) hmmm, as I reread them, they don't make sense to me.

How do you calculate each scenario?

Steve=True

formula is has not been decided yet, based on my understanding of excel I believe we can change that later.
For a filler I will use the following formula

delta = Losers_starting_points - Winners_starting_points
Points exchanged = f(delta) = 10+(0.1*delta), if tie f(delta)=(0.1*delta)

I want to be able to put he info on the left and create the chart on the graph on the right.
Note: graph is scaled for time, no for match numbers.

FYI: I this case I had to manual insert the information and make it.


Thanks again. I hope this clarifies what I am looking for.


Dr. Excel Class of 2035

 

[DrExcel2035]

Just in case you needed this, this is how I created that graph mentioned in previous post.
This is the only way I know how to create it.

This way long, tedious, and unforgiving of any errors that are not discovered immediately.
Also I don't want to repeat the information again and again to get the time scale correct, like I did here the information from 1/11/2015 through 1/23/2015 is identical.

Please tell me that there is an easier way to do this.

Thanks again.
Dr. Excel Class of 2035