## Create a random playlist in excel – True round-robin tournament

**Mark G asks in Create a random playlist in excel:**

Can this example be modified to create a true round robin draw? i.e. using a 6 team scenario, for any round there will be three games/matches, but obviously Team A could not compete against say Team B and Team C during that round as can occur using the above formula.

**Answer:**

A **round-robin tournament** or **all-play-all tournament** is a type of tournament in which each contestant meets all other contestants in turn. *Source: Wikipedia*

This example is on a double round-robin basis, in which every team plays all others in its league once at home and once away **randomly**.

I am very close to a solution but I am stuck. This is as far as I get.

**Problem:** Sometimes a #num error is rendered in column D.

### Array formula in cell C2:

### Array formula in cell D2:

### This is how the formula in cell c2 and d2 works (simplified)

**Index**(Teams, **Large**(**Row**(Teams)*(*Criterion1*)*(*Criterion2*)), **Randbetween**(1, **Sumproduct**((*Criterion1*)*(*Criterion2*)))) + CTRL + SHIFT + ENTER.

The formula selects a team randomly in column C and column D, with the use of each criterion.

**Formula criteria in column C**

- COUNTIF($C$1:C1, $A$2:$A$5)<3. Each team can only play each opponent once, thus totally three times in column C.
- COUNTIF($C$1:D1, $A$2:$A$5)<ROUNDUP(ROWS($C$1:D1)/2, 0). Each team can only play once in a round

**Formula criteria in column D**

- COUNTIF($D$1:D1, $A$2:$A$5)<3. Each team can only play each opponent once, thus totally three times in column D.
- COUNTIF($C$1:D1, $A$2:$A$5)<ROUNDUP(ROWS($C$1:D1)/2, 0. Each team can only play once in a round
- COUNTIF(C2, $A$2:$A$5)=0. A team can not play against it self
- ISERROR(MATCH($A$2:$A$5, IF(C2=$C$1:C1, $D$1:D1, ""), 0). There can not be any duplicate matches. Each team can only play once against each opponent "home" and once "away".

Sometimes the formula creates a true round-robin tournament, sometimes it creates a #num error in column D. This is because a random team in column C is not valid. It creates a duplicate match and returns a #num error in column D.

If I add another criteria in column C containing adjacent cell in column D a circular reference is created.

### Download excel sample file for this tutorial

true round robin draw.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**LARGE(**array,k**)** returns the k-th largest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**RANDBETWEEN(**bottom, top**)**

Returns a random number between the numbers you specify

**ISERROR(**value)

Checks whether a value is an error and returns TRUE or FALSE

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

### Category: Random

How to create a list of random unique numbers in excel

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]Comments(102) Filed in category: Excel, Random

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel

Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6-from-49 lotto, a […]Comments(16) Filed in category: Excel, Random

Create a random playlist in excel

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]Comments(10) Filed in category: Excel, Random

Dynamic team generator in excel

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]Comments(9) Filed in category: Excel, Random, Team generator

Comments(7) Filed in category: Excel, Random, Team generator

Comments(4) Filed in category: Excel, Random, Team generator

Sort a list in random order in excel

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]Comments(4) Filed in category: Excel, Random, Sort values

Assign each person with randomly unique objects as a daily activity

Vijay asks: I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects […]Comments(3) Filed in category: Excel, Random, Unique distinct values

Generate list of random dates with criterion in excel

Overview Today we are going to build an array formula. Step by step creating random dates with a criterion. The criterion […]Comments(2) Filed in category: Dates, Excel, Random

Random row from a boolean matrix in excel

Question: It’s the kind of thing I do regularly in matrix oriented languages (APL, K, Gauss etc), but I can’t […]Comments(0) Filed in category: Excel, Random