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:

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

Related posts:

Create random dates, Mon to Fri, within a year in excel

Sort a list in random order in excel

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

Create a random playlist in excel

How to create a list of random unique numbers in excel