2

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?

3 Answers 3

3

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.

enter image description here

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.

enter image description here

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

enter image description here

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)

enter image description here

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.

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

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.

enter image description here

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)

To learn more about macros in general, see:

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!

4
  • I'm currently doing this procedure in google sheets. Do you know if this is possible to do in google sheets?
    – Babado
    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?
    – Babado
    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
1

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.