Creating a schedule?

[Kenja824]

I have been trying to figure out a way to create an adaptive schedule for someone who runs a Cornnhole league. The site they use to use to make it was merged with another they dont like. So he was hoping to have it on an excel spreadsheet.

The amount of teams can vary from year to year.

Im not sure how many weeks it will go.

Attached is one I found on the internet but it has some issues....
1) It is quite confusing to me to adapt it.
2) It has a limit of 20 teams. His leagues have had as many as 18 teams and so he would want a few extras in case they have an extra large league.
3) Some years they play best of three each week and I dont see how to incorporate that.
4) Its standings only give you the total wins. No tie breakers. He would want total points scored as a first tie breaker and fewest points against as a 2nd tie breaker.

NOTE** The attached version, on the MATCHUPS tab, everything below row 50 is what I added to try and figure out how to rank the teams according to total wins and points scored. I didnt do so well. lol

I have spent hours trying to figure out how to create a more simpler version and cant even figure out how to make a simple schedule much less one that will adapt to how many teams there are.


If anyone has something like this already created, and would be willing to share it, I would appreciate it.

If the attached file can be adapted to work with more teams and add rankings, that would be great.

If someone can at least show me the formulas needed to create the scheduling part so every team plays each other once before repeating, as well as adapting to total weeks, that would work to.

Thanks for any help anyone can offer.

I really didnt anticipate this being so difficult. lol Make a schedule. How hard can it be right?



Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[BridgeSmith]

I can't really follow what you're trying to accomplish, but I'll give a few pieces of general advice from my own experience that may help.

1) Investigate the Vlookup function. It may be a more direct way to find the information you want to put into the tables.

2) Try using the custom formatting to 'label' numbers. So, rather than putting "Team14" in the list of teams, you would just enter "14", then you can manipulate it as a number. When you get to where it's displayed, you would use a custom format of ---> "Team"0

It will display the number 14 as "Team14", but it's still the number 14 in the cell.

3)For putting multiple text strings and/or cell contents together, try using the ampersand (&) to join the strings. For instance to show the matchup in cell C5 of the teams in B5 and D5, you'd use ---> =B5&" vs "&D5. It's similar to the concantenate, but I find it easier to use and easier to see what I've done.

 

[SkipVought]

Ken,

Is what you need is a template for
1) a list of teams,
2) number of games per team per week,
3) number of weeks
as input data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Skip, that does sound about right.


I did learn a fairly easy way to come up with a schedule....

Whatever the team names are, they will be given a number. Then you just arrange the numbers with the first half of them going left to right. Then the second half going right to left just below them. Then each new week teams 2 through the last team will rotate once spot, leaving team 1 in its same slot.

Week 1
1 2 3 4 5
10 9 8 7 6

Week 2
1 10 2 3 4
9 8 7 6 5

Every team would play each other once before repeating.

-----------

In the attached file, I have it set to create a schedule for 30 teams over 20 weeks.

On the right of the schedule, there is a formula where you can type in any team number and get its opponent for that particular week.

I have not had a chance to work on it this weekend like I wanted to. I need to devise a way to get this info for each team for each week. I dont believe it will be too difficult to figure out.

I believe I have the hardest parts figured. If you know a better way to do this with code or something, that would be great, but if not, then I think I might be able to get it from here if I can find more time to work on it.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[Kenja824]

Skip

Oh there is one thing I have not figured into my schedule yet. If there is an odd number of teams there will need to be a bye week for one team every week. I dont see that as toooooo much of a problem. But if you or anyone else makes something up with code, you will need to add that into it.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[davidbeach]

You’d have to have an even number of teams, but one of them can be named “bye”.

When one this sentence into the German to translate wanted, would one the fact exploit, that the word order and the punctuation already with the German conventions agree.

-- Douglas Hofstadter, Jan 1982

 

[Kenja824]

True, that is a good idea. I am going to make a list for the team names. Each team will get a number to represent it. If the total teams is an odd number, I will just have to name it "Bye". That sounds easy enough. Thanks

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[Kenja824]

Just noticed the schedule maker I last loaded does not work right. It looks like it at first but as you scroll down you will find the later weeks has two number ones and such.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[IRstuff]

Seems like everything you are suggesting is already in the spreadsheet you posted. There were an odd number of teams and a "bye" team was added to even things up, and teams rotate opponents as described above.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[3DDave]

Something makes no sense.

There are 435 combinations for 30 team pairings; playing 15 games a week requires 29 weeks.

 

[Kenja824]

The original file was good for up to 20 teams. The guy I am doing this for had a league with 18 before. I just want to make it larger to handle more if he happens to have a larger league.

The standings in the original file was very weak. It gave wins and thats it. I want to create a standings that can show wins as well as points scored and points against for tie breakers.

Mainly the original file was very confusing to me on how anything worked. I was looking for a simpler way to create one.

3DDave,

Each team does not have to play every other team. It just needs to be set up so that no teams will repeat until they have played every other team once. So if a league is only 17 weeks and there are 20 teams, it is okay that they wont play every team. We just dont want any team playing another team multiple times when there are teams they have not faced.

Currently the file I am attaching here this time is going well. I believe I may even be able to get the rest on my own. Maybe. lol The hardest part I think will be getting the standings to show the best team down to the worst, using tie breakers. I can do large formulas for the wins when I get to that, but I am not sure how to do a large that will look at points scored then fewest points against to break ties.

Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[BridgeSmith]

I can do large formulas for the wins when I get to that, but I am not sure how to do a large that will look at points scored then fewest points against to break ties.

This is easily done using the sort command with multiple sorting criteria. Sort first by wins or win/loss record, then by points. You can automate the process by recording a macro as you do it once, create a button, and assign the macro to the button. Then each time you update the scores, click the button, and it will put them in order based on the current standings.

Edit: a note about macros - the cell references in macros are not updated when you move cells around, so if you move the table that you want it to sort, you have to update the macro script (the program text) manually, or record a new macro.

You can assign numerical values to wins, ties and losses (1, 0.5, and 0 or 1, 0, and -1) for the purposes of calculating the records.

 

[Kenja824]

BridgeSmith

I havent given the sort much thought because I was hoping to use formulas to make it automatic. But if I cant make it automatic, then that is definitely an option to look at. Thanks.


Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

 

[BridgeSmith]

As I said, setting up a macro to re-sort the team standings is very easy if you record the macro. You start the recording, do the steps you want it to perform, and then stop recording. Excel writes all of the code for you.

 

[3DDave]

Use a pivot table to deal with the data sorting.

 

[rb1957]

sorry, having trouble getting past "cornhole league" ... yeah, 12yo humour !

this should be posted in the "engineers with hobbies" forum.

a league play is not, IMHO, a "schedule". but first decision, is it round robin (every team plays every other team) or pools ?

"Hoffen wir mal, dass alles gut geht !"
General Paulus, Nov 1942, outside Stalingrad after the launch of Operation Uranus.

 

[SkipVought]

Ken's got Excel questions.
Well "Engineering spreadsheets" not engineering, eh.
Ken, if they chase you away, check out

https://www.tek-tips.com/threadminder.cfm?pid=68

the Tek-Tips sister site in the Microsoft Office Forum.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

rb1957

Since I am building this scheduling in excel, I figured this would be the place to ask how to do those things in excel. Even though it is a personal project, I and the guy I am doing this for are in the engineering field. And being slow on work right now, I am doing it on work hours... off and on. Doesnt that technically pass?

Not sure what pools is. I would classify it as Round Robin but its not that every team has to play each other. Just that we dont want any teams repeating matches unless they have played every team. There is a fair chance there will be more teams than weeks.

------------

I will check it out Skip

-------------

I am much closer now to getting this figured out. I was able to spend a few extra hours on it today. I would be further ahead but I keep finding where something isnt calculating as I expected and have to redo the formulas. I think I have it calculating the schedules right now. Now its all about calculating the standings and such.



Ken

http://www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.