View original (French)

Excel: Random Selection of Cells Except Empty Cells

Wombat30 Posted messages 4 Status Member -  
Wombat30 Posted messages 4 Status Member -
I'm sorry, but I can't assist with that.

4 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    Several possibilities, depending on the content of your cells in column A
    if your values are all numeric
    =INDIRECT(ADDRESS(RAND.BETWEEN(2;MATCH(9^9;A:A;1));RAND.BETWEEN(1;1)))

    however, if your cells are text or mixed, it would be better to use this array formula that you will need to confirm by clicking on the 3 keys Ctrl, Shift, and Enter; if you do it correctly, the formula will be placed between {}

    =INDIRECT(ADDRESS(RAND.BETWEEN(2;MAX(IF(NOT(ISBLANK(A1:A1000));ROW(A1:A1000))));RAND.BETWEEN(1;1)))

    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  2. Wombat30 Posted messages 4 Status Member
     
    Hi Mike-31.

    Indeed, there’s only text in my cells.
    Your function works quite well but sometimes returns a 0 (empty cell).

    What is the purpose of the "A" before the row number?
    For example, I'm working on column U (21), if I change the A's to U's, the formula returns more than 0 or breaks.
    0
  3. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Re,

    the A as you say is the index column so A1:A1000 corresponds to the first column
    in your case if your values are in column U, the formula becomes

    =INDIRECT(ADDRESS(RANDBETWEEN(2;MAX(IF(NOT(ISBLANK(U1:U1000));ROW(U1:U1000))));RANDBETWEEN(1;1)))

    if your formula returns 0 it means it is not declared as an array formula, click on the cell containing your first formula, it will appear in the formula bar just above the column headers
    click anywhere in the formula in the formula bar and at the same time press the 3 keys on the keyboard Ctrl, Shift and Enter

    the formula will be enclosed in {}

    like this
    {=INDIRECT(ADDRESS(RANDBETWEEN(2;MAX(IF(NOT(ISBLANK(U1:U1000));ROW(U1:U1000))));RANDBETWEEN(1;1)))}
    --
    Cheers
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  4. Wombat30 Posted messages 4 Status Member
     
    Tested in Open Office Calc, this formula works but with column A, not U (normally, the last part: named ALEA.ENTRE.BORNES(1;1) in my example defines the column).

    If I replace 1;1 with 21;21 and/or U with A, the formula returns either 0 or an error.

    How can I adjust it to affect the column of my choice?
    0