Excel Templates | Excel Spreadsheets


Free Excel Templates

Fixtures List Creator

Posted on May 9, 2009
Filed Under Sports | 38 Comments

Share

There are some comments and requests through email asking about the possibility of having automatic fixtures list in my football standing league creator table. So, if they finished filling the name of the teams, fixtures list will be automatically created, so they don’t have to manually typing the team’s name and thinking about team’s matches schedules.

In the beginning, I created that spreadsheet to automate the standing table based on ready made matches or fixtures. But the people who using this template is correct about asking possibility of creating totally new competition and matches where they have to design all from the scratch.

Understanding that concerns, I tried to figure out about automate this fixtures list. And I found out that creating fixtures list is not an easy task. Especially if it is involved many teams, or when the number of teams are odd where impossible to have all matches at the same time.

It took several nights just to find the logic to create this fixtures list manually, and it took additional nights to find the right formula and function in excel to map the logic without using excel macros. Fortunately Manchester United vs Arsenal and Barcelona vs Chelsea Champion League matches accompany me to stay awake with matches results I expected :-) .

This spreadsheet can be used to create matches schedules for other sport events that have competition style, like volley ball, futsal, badminton, basketball or others.

This spreadsheet can create fixtures lists for maximum 40 teams. The reason I limit the teams until 40 teams because of the size of excel file. I don’t feel comfortable creating the template with filesize more than 1.5 Mb, but this is a consequence if you create excel template with many “if condition” without macros. If you want to create the fixtures lists with the number of teams more than 40, you can continue the formula I created inside the spreadsheet.

This spreadsheet only consists of three worksheet, one is setup worksheet where you have to set the number of your teams, the competition type and your team’s name. There is two choices of competition type, full competition where each team will meet twice, or half competition where each team only meet once.

Fixture List Setup

Read more

Tags: , , , , , , , , , , , ,

Nascar Standing Table

Posted on May 9, 2009
Filed Under Sports | 6 Comments

Share

Two weeks ago, I watched Nascar race on my TV. The race is taken place at Talladega Superspeedway where the winner is Brad Keselowski. The race is very competitive where within 188 laps, there 25 different racers were leading for at least one lap. By using common car, the race is looked like a real race and more physical comparing with Formula 1 race. Probably that is the reason why American people like Nascar racing more than Formula 1 racing.

After watching the race and seeing the standing result, I googling for the Nascar points rule and regulation to find out the point system of Nascar race and find the difference between Nascar and Formula 1. And it is more complicated than I thought, but it gave me the reason to map it into the excel. Perhaps, if you are Nascar fans, you know better than me, and you can help me if I incorrectly interpreted the rule.

I read that Nascar point system is changing frequently over some period of time, so it will make it difficult to make the common template like I made for F1 to be used yearly. So, the rule and regulation I used in this template only valid for 2009 series. You have to check the rule and regulation again if you want to use this template for 2010 series.

I am just focusing on Sprint Cup racing, and basic point systems for year 2009 that I summarize from Nascar official site and Wikipedia are as follows:

I made this template only for Sprint Cup Series and only make the drivers standing table. If you want to have standing tables for Nationwide and Camping World Series, you can modify my template. And if you need owner or manufacture standing, you can add it by yourself inside the template.

I created this spreadsheet with the same approach with creating the football standing table. At the beginning, I thought it won’t take many “if condition” that will affect the size of the file, but after I mapping all the logic into the formula, it needed more “if condition” than I thought and that make the filesize very big. Usually I always try to reduce the filesize by finding other alternative formulas, but this time I skip it. Probably if you are also an excel lover, you can help me reducing the filesize by finding alternative formula or perhaps make different approach.

This spreadsheet consists of 9 worksheets
1. Drivers
2. Race Schedules
3. Race to Chase Results
4. Race to Chase Standings
5. Top 12 Progress Chart
6. Sprint Cup Results
7. Sprint Cup Standings
8. Dummy Sprint Cup (hidden)
9. Dummy Race to Chase (hidden)

1. Drivers Worksheet
This is a place where you can put all Nascar drivers information. And there is one column for penalty or deduction points in case there are some drivers that eligible for points in the race but the points is erased because of rules. Fortunately there is a sample for this, where I have to find out why Tony Raines didn’t get a point while rank in 41st position in the Crown Royal Presents the Russ Friedman 4 race. I googled it just to find only one explanation that Tony Raines was late entry in that race.

Nascar DriversI put all Nascar Sprint Cup race schedules as reference here. And it is just plain schedules including non-counting point races. I put a date conditional formatting inside where the colour of date will turn grey if the date is passed.

Read more

Tags: , , , , , , , , , , , ,

Create Sports League Standing Table

Posted on March 29, 2009
Filed Under Tutorial | 47 Comments

Share

Finally I finished my tutorial on creating sports league standing table using Microsoft Excel, as promised. You can use this tutorial not only for creating soccer or football competition like my sample, but also you can use it for any competitions that follow the same schemes.

Step 1 : Define Participant Teams
At the beginning, you need to define the number of teams that participate in the competition. I define 8 teams as participants in this tutorial

Step 2 : Define Competition or League Type
You have to define the competition type. You will need this to control your fixtures table. Although this one has no direct effects in the formula, it will give you additional concerns when you need additional factor to decide the competition winner, just in case there is a tie position for some teams when all competition matches are completed. For example, if you pick full competition type where each team will meet twice in home and away game, you can make the goal made by away team in away game have more weight than goal in home game as decision factor to rank the team.

Step 3 : Define Competition Rule
You have participants, you already defined the competition type, now, you have to define rules needed to rank the team . And in this tutorial, I use the following rules to define the standing position :

Competition Basic Rules :
1. The final rank will be decided after all matches are finished
2. A team will get 3 points for winning, 1 point for draw and no points for defeat

Competition Standing Rule :
1. One team will be above other teams if it has more points than other teams
2. If there are two or more teams have the same points, the higher rank will be decided by better goal differences
3. If there are two or more teams have the same points and the same goal differences, the higher rank will be decided by better goals scored for
4. If there are two or more teams have the same points, goal differences, and goals scored for, higher rank will be decided by better away goal scored

You can add more rules for the competition to define the ranks, because once you know how to formulate it, any rules can be interpreted into excel formula easily.

Read more

Tags: , , , , ,

Page 35 of 42« First...102030...3334353637...40...Last »