Excluding cells using RANDBETWEEN

[frankstr]

Hello all. I have two columns of data with around 200 rows, all text. Column A has no blank cells, but column B does. I'm using the following formula to randomly select a cell from column A:

=INDEX(A2:A210,RANDBETWEEN(1,ROWS(A2:A210)))

The problem is, I need to exclude results where there is data in the corresponding cell in column B. i.e. I need to only show results from column A where the corresponding cell in column B is blank.

Is there a way to alter the formula I'm using to get this result?

Thanks very much in advance

 

[jasonb75]

Perhaps something like

=INDEX(A:A,AGGREGATE(15,6,ROW(A2:A210)/(B2:B210<>""),RANDBETWEEN(1,COUNTIF(B2:B210,""))))

 

[Phuoc]

Maybe

=INDEX(A:A,AGGREGATE(15,6,ROW(A2:A210)/(B2:B210=""),RANDBETWEEN(1,COUNTBLANK(B2:B210))))

 

[frankstr]

Thanks Jason and Phuoc for both of your replies. Phuoc's formula gives exactly the results I was looking for. Jason's filters things slightly differently, but I'm running Excel Starter 2010, so maybe that has something to do with it. This stuff is like another language to me, so thank you both for your kind help.

 

[jasonb75]

That was an error in my formula, I misread part of your question and edited the formula after posting my reply but didn't do it correctly. It should have been

=INDEX(A:A,AGGREGATE(15,6,ROW(A2:A210)/(B2:B210=""),RANDBETWEEN(1,COUNTIF(B2:B210,""))))

Both formulas do the same thing, but we used different methods to count the number of blank cells.