Geek Answers Handbook

Random cell in cell range - Excel

I am trying to implement the Montecarlo method in Excel. I need to select a random cell, but from a range of cells, for example, from A1 to P4, because inside this range of cells I have numbers that I need.

Thank you very much

+4
source share
4 answers

How can I do this is to use the RAND () function in two columns (one for rows, one for columns).

Using the cells you need, in the first column say R2: R5, put in RAND (). In S2: S16 (or, no matter how many cells there are between A and P ..), do the same.

S1 R1 RANK R2 S2 . INDEX .

, RAND . , , .

+4

test :

ROW, ROWS, COLUMN COLUMNS, , , RANDBETWEEN, , ADDRESS, A1, INDIRECT, :

=INDIRECT(ADDRESS(RANDBETWEEN(ROW(test),ROW(test)+ROWS(test)-1),RANDBETWEEN(COLUMN(test),COLUMN(test)+COLUMNS(test)-1)))

:

=INDIRECT(
    ADDRESS(
        RANDBETWEEN(
            ROW(test),
            ROW(test)+ROWS(test)-1
            ),
       RANDBETWEEN(
            COLUMN(test),
            COLUMN(test)+COLUMNS(test)-1
            )
       )
)
+2

, " ". - vba (, VBA ). , A1: P4 .

Sub GetRandomCell()

    Dim RNG As Range
    Set RNG = Range("A1:p4")

    Dim randomCell As Long
        randomCell = Int(Rnd * RNG.Cells.Count) + 1

    With RNG.Cells(randomCell)
        .Select
        .Interior.Color = vbYellow
    End With

End Sub
+1

- :

=INDIRECT(CHAR(INT(RAND()*14+65))&CHAR(INT(RAND()*4+49)))

, 14 4 * Sign

0

More articles:


All Articles