Return a Random Column Value

**John Bates** · 03-16-2010, 03:56 PM

Hi,

I wrote the following formula in cell F2: IFERROR(INDEX(DW.Data!1:1,0,MATCH(M1,DW.Data!1:1,0)),0).

For the row option of the index formula, I need to add some logic that will basically choose a random row to return among ONLY rows that have values. The column will have maybe 10,000 rows with 9,652 rows being blank (no value) and 348 rows have a value. The values within the row will be noncontiguous.

I need the above formula to offset and choose any random cell containing a value and return it.

I've attached an example. THANKS FOR ALL YOUR HELP!!

**contaminated** · 03-16-2010, 04:51 PM

Maybe this one

=INDEX(B2:B10000,SMALL(IF(B2:B10000<>"",ROW(INDIRECT("1:"&ROWS(B2:B10000)))),RANDBETWEEN(1,SUM((B2:B 10000<>"")*1))))

Confirm with ctrl + Shift + enter

**NBVC** · 03-16-2010, 04:59 PM

This seems to work:

Code:

=INDEX(B2:B10000,SMALL(IF(B2:B10000<>"",ROW(B2:B10000)-ROW(B2)+1),RANDBETWEEN(1,COUNTIF(B2:B10000,"*?"))))

confirmed with CTRL+SHIFT+ENTER

You have some cells in B range with 0, remove those.. or you may get 0 returned sometimes.

**martindwilson** · 03-16-2010, 05:20 PM

i'd opt for a helper column in say col b
if(a1>0,rand(),"") dragged down then match for use in the index row criteria as MATCH(MIN(B1:B10000),B1:B10000,0) [or MATCH(max(B1:B10000),B1:B10000,0)]

**NBVC** · 03-16-2010, 05:30 PM

The array formula takes mere milliseconds to refresh upon hitting F9.

If you have many other formulas evaluating in the sheet or if you have lots more than 10000 rows, then perhaps helper column would be more ideal....

**John Bates** · 03-16-2010, 06:02 PM

Perfect, thank you for both of your help. I ended up using contaminated's array formula for this particular problem though both worked equally well.

Thanks again!!!