How to randomly select from a list with condition

With 2 helper columns, you can try something like this:

A3:A20 = your data

B3 = IF(A3=0,500,ROW()) (Copy down)
C3 = SMALL(B$3:B$20,ROW()-2) (Copy down)

D3 = OFFSET(A3,INDIRECT("C"&RANDBETWEEN(ROW(),ROW()+COUNTIF(C3:C20,"<500")-1))-ROW(),0)


Hope this helps.

Originally Posted by kathyxyz

I have a list of twenty real number in A1 to A20.
How can I randomly select a number from the list, but not the one with value = 0
If the selected number is zero, it will automatically select another random number from the list.
The list is dynamic, so I don't know exactly when and where the ones with zero value show up.
Thank you.

Katherine.