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.


--
kathyxyz
------------------------------------------------------------------------
kathyxyz's Profile: http://www.excelforum.com/member.php...o&userid=25624
View this thread: http://www.excelforum.com/showthread...hreadid=390385

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()+COU NTIF(C3:C20,"<500")-1))-ROW(),0)


Hope this helps.



kathyxyz Wrote:
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.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390385

Thanks, Morrigan!

Katherine


--
kathyxyz
------------------------------------------------------------------------
kathyxyz's Profile: http://www.excelforum.com/member.php...o&userid=25624
View this thread: http://www.excelforum.com/showthread...hreadid=390385

It can also be calculated in a single cell:

=INDEX(LARGE(A1:A20,ROW(INDIRECT("1:"&(COUNT(A1:A2 0)-COUNTIF(A1:A20,0))))),RANDBETWEEN(1,COUNT(A1:A20)-COUNTIF(A1:A20,0)))


Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390385

Just a quick note if you are using Olasa's formula, all data must be
positive numbers.


olasa Wrote:
It can also be calculated in a single cell:

=INDEX(LARGE(A1:A20,ROW(INDIRECT("1:"&(COUNT(A1:A2 0)-COUNTIF(A1:A20,0))))),RANDBETWEEN(1,COUNT(A1:A20)-COUNTIF(A1:A20,0)))


Ola Sandström


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390385

Thanks for spotting that Morrigan.
This works with both positive and negative number

=INDEX(LARGE(IF(A1:A20=0,"",A1:A20),ROW(INDIRECT(" 1:"&(COUNT(A1:A20)-COUNTIF(A1:A20,0))))),RANDBETWEEN(1,COUNT(A1:A20)-COUNTIF(A1:A20,0)))

However this formula MUST be confirmed by holding down Ctrl and Shift,
and then hit Enter.

Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390385