## 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 make it work in excel. It would be a very handy construct to have around (if indeed, it’s possible).

I have a rectangular Boolean matrix M that is say 30x6

For example, one column of M is:

1

0

0

1

0

1

For each column, I want to randomly choose a row number of a cell with a 1 in it.

For the example column, the result would be a 1, 4,or 6

With M, I’d end up with 30 numbers (one for each column).

I don’t want to use VBA, and I need the calc to be done in one cell/column without using a second matrix of random numbers to generate indices)..it needs to be done on the fly so to speak.

If u multiply each column by a 6x1 array of random numbers, and find the position of the largest, that would work, but I’m having trouble with the syntax to get it done in one cell. I suspect it should be a cousin to the syntax for removing blank cells.

Have u seen something like this before or is there an obvious way to do this? thanks

**Answer:**

**Array formula in A8:**

=LARGE(ROW(1:6)*A1:A6, ROUND(RAND()*SUM(A1:A6)+0,5, 0)) + CTRL SHIFT + ENTER. Copy cell A8 and paste it to the right.

This formula uses LARGE function to select a random row number where the cell value equals 1.

**Edit:**

**Edit:**

**Alternative array formula in A8:**

**Alternative array formula in A8:**

*=LARGE(ROW(1:6)*A1:A6, RANDBETWEEN(1, SUM(A1:A6))) + CTRL SHIFT + ENTER. Copy cell A8 and paste it to the right.*

### Explaining alternative array formula in cell A8

=LARGE(ROW(1:6)*A1:A6, RANDBETWEEN(1, SUM(A1:A6)))

**Step 1 - Create an array of possible row numbers**

=LARGE(**ROW(1:6)*A1:A6**, RANDBETWEEN(1, SUM(A1:A6)))

ROW(reference) returns the row number of a reference

ROW(1:6)*A1:A6

becomes

{1;2;3;4;5;6}*A1:A6

becomes

{1;2;3;4;5;6}*{0;1;0;1;1;1}

and returns

{0;2;0;4;5;6}

**Step 2 - Calculate randbetween range**

=LARGE(ROW(1:6)*A1:A6, RANDBETWEEN(1, SUM(A1:A6)))

SUM(number1,[number2]) adds all the numbers in a range of cells

SUM(A1:A6)

becomes

SUM({0;1;0;1;1;1})

and returns number 4.

**Step 3 - Return a random number**

=LARGE(ROW(1:6)*A1:A6, **RANDBETWEEN(1, SUM(A1:A6))**)

RANDBETWEEN(1, SUM(A1:A6))

becomes

RANDBETWEEN(1, 4)

and returns a number between 1 and 4

**Step 4 - Return a random row number in cell A8**

=LARGE(ROW(1:6)*A1:A6, RANDBETWEEN(1, SUM(A1:A6)))

becomes

=LARGE(ROW(1:6)*A1:A6, 2) [*random number]*

becomes

=LARGE({0;2;0;4;5;6}, 2)

and returns number 5 in cell A8.

**Download excel sample file for this article. **

random row.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

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

**ROW(**reference**)** returns the row number of a reference

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**RAND()**

Returns a number greater than or equal to zero and less than one, evenly distributed

**ROUND(**number, num_digits**)**

Rounds a number to a specified number of digits

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

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 […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]

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 […]

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

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 […]

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 […]

Generate list of random dates with criterion

Overview Today we are going to build an array formula. Step by step creating random dates with a criterion. The criterion […]

True round-robin tournament in Excel

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form