# Find position of a random zero in a matrix

Imagine I have a matrix in Excel, i.e., an array in the interval A1:E5. Each cell has either a 0 or a 1. How can I find the position of a random zero in that array?

I saw that using the Excel function SUMPRODUCT, I would be able to find the row and column to which a certain value corresponded. The problem is that this procedure would only work if the value I was looking for was unique.

Here's an example of what I want:

Consider that my data is

row A B C D E
1 0 1 0 1 1
2 0 0 0 1 1
3 1 1 1 1 1
4 1 1 1 1 0
5 0 1 1 1 1

I would like to have a function that would select a random output from the set `{(1,A),(1,C),(2,A),(2,B),(2,C),(4,E),(5,A)}`.

By function I mean an expression in an Excel cell.

Anyone knows how to do this?

The macro outlined above/below seems to be the easiest method. If you cannot use a macro, then here's a tedious method.

Here, columns G thru K represent the cells with a value of 0 in columns A through E.

Cell G2 formula:

``````    =IF(A2=0,ADDRESS(ROW(),COLUMN()-6),"")
``````

Drag it across and down. Next, pivot the data (using a non-traditional pivot method), where you click on any of the green columns. Then press ALT-D and then P. This will bring up a Pivot Wizard.

Select "Multiple Consolidation Ranges" and click NEXT.

Then click on "Create a single page field for me" and press NEXT. For your data range, include a blank column to the left of the green columns. In this case, range is F1:k6. Click NEXT.

Finally, select where you would like to place this pivot table, such as cell M3. Place the "Value" field in the Rows area.

Last step, create a formula to select a random value from your pivot area. Cell P1 formula:

``````    =INDEX(\$M\$5:\$M\$11,RANDBETWEEN(1,COUNTA(\$M\$5:\$M\$11)),1)
``````

Since this is a randbetween formula, it will change on every click in the sheet. Yes, a tedious task and even worse if you have many columns.

NOTE: Instead of using a Pivot table, you can use a formula to extract unique values from your green columns, but the formula can get quite long if you have many columns. Just look up extracting distinct values from multiple columns.

• ...............A very clever approach ! (+1) Sep 8, 2021 at 19:21

Try the following User Defined Function:

``````Public Function RanZero(rng As Range)
Application.Volatile
Dim r As Range, temp As String
Dim arr, N As Long
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction

For Each r In rng
If r.Value = 0 Then
temp = temp & "," & r.Address(0, 0)
End If
Next r

temp = Mid(temp, 2)
arr = Split(temp, ",")
N = wf.RandBetween(LBound(arr), UBound(arr))
RanZero = arr(N)
End Function
``````

It gathers the addresses of all the cells in the matrix with zero value and returns a random sample.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

• I'm currently doing this procedure in google sheets. Do you know if this is possible to do in google sheets? Sep 8, 2021 at 16:15
• @Babado You would need to develop an equivalent function in the Google Script language. Sep 8, 2021 at 16:17
• Is there a way to do it without a macro? Is there a way to start looking at a random position for the first zero? Sep 8, 2021 at 16:21
• @Babado It may be possible, but I don't know how to do it. Sep 8, 2021 at 16:28

If you've latest version of Excel 365 available (with `LET()`) AND all your columns contain at least one 0, then you can use this one cell formula to pick a random cell containing 0:

`=LET(col,RANDBETWEEN(1,COLUMNS(A1:E10)),rows,FILTER(ROW(INDEX(A1:E10,0,col)),INDEX(A1:E10,0,col)=0),ADDRESS(INDEX(rows,RANDBETWEEN(1,COUNT(rows))),COLUMN(A1)+col-1))`

how it works:

• `col,RANDBETWEEN(1,COLUMNS(A1:E10))` picks a random column from the range and stores it in variable `col`
• `rows,FILTER(ROW(INDEX(A1:E10,0,col)),INDEX(A1:E10,0,col)=0)` calculates row numbers which are 0 in the selected column and stores in `rows`
• `ADDRESS(INDEX(rows,RANDBETWEEN(1,COUNT(rows))),COLUMN(A1)+col-1))` generates cell reference