Functions

Examples :	Keeping totals for a Soccer League in Excel
Description :	Array Formulas are used extensively to figure out the wins, losses, ties, points against, points for, and even a rating points scale - if you come up with an equation to rank the teams.

Someone sent a question saying they wanted to track the league soccer stats. They were wondering if there was an easy way to analyze data across an excel sheet. Well, that's what Excel is all about.

First we established the team names (in blue below). Highlight A3 through A13 and change the name of this range to TEAM. Don't worry about the ranking right now. Next we set up the games as they happened and put the scores down. To make sure the data was consistent (which is necessary for our formulas to work) we put the names in a drop down list and made them match the list. Highlight D2:D21 and F2:F21 and set the drop down list and validation at the same time. DATA menu VALIDATION choose LIST with RANGE of =TEAM

All the formulas are ARRAY formulas. The first column (Games Played) just checks to see if there is an entry in the PTS column

=SUM(($D$2:$D$21=A25)*($E$2:$E$21<>"")*(1))+
SUM(($F$2:$F$21=A25)*($G$2:$G$21<>"")*(1))

This basically translates to: check cells D2 through D21 and see if A25 (team A) is the home team, then check cells in F2 through F21 and see if team A is the visiting team. The (1) at the end basically counts all of the occurrences of team A being the home or visiting team.

The team ranking was done with the excel RANK formula. If two teams tie for 2nd place, they are both ranked second. The last six teams in our example haven't played any games so they are currently all tied at 5th place.

Each of the formulas in ROW 25 have been entered with Absolute and Relative references so that the formula can be "filled down" for the rest of the teams (rows 26 through 35).

Have more games - insert them into the body of the current table and it will automatically change the formulas to include them. Insert rows in row 17.

Have more teams - be careful how and where you add them. Make sure they are listed in the validation. You will have to redefine what the TEAM range refers to.

We don't use it but if you find it useful or think something is missing let me know.

Get the complete file as seen above
(macro and virus free) approximately 36k.