Pick Random Value from a Range (not including empty Cells)

[ghoneim]

Hi,
I've come across this formula that picks random cell value from a determined range -say from column (A) to (C) - the whole columns

=INDEX($A:$C,RANDBETWEEN(1,COUNTA($A1:$A65536)),RANDBETWEEN(1,3))

However, it seems like when a cell is empty in the middle of range (say in the middle of column B), it may get picked as well returning 0 as value.
Is there any work-around to limit random selection only on cells that contain values?

I'd appreciate your help.

[ghoneim]

any thoughts?

[shg]

Brutal formulas:

Please Login or Register  to view this content.

In E1 and copied through G1:

=INDEX(A:A, LARGE(IF(A:A<>"", ROW(A:A)), RANDBETWEEN(1, COUNTA(A:A))))

G2:

=INDEX(E1:G1, MATCH(RANDBETWEEN(0, COUNTA(A:C)-1), CHOOSE({1,2,3}, 0, COUNTA(A:A), COUNTA(A:B))))

[ghoneim]

Problem Solved!
Thanks a lot