## Random row from a boolean matrix in excel

*Article updated on April 04, 2011*

**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

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 […]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 […]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 […]### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article