Returning random number from list formula

[junk87]

Without the use of VBA, I have been trying to write a formula that returns a random value, from a column of values, ecluding 0's.

Anybody know how to do this, without the use of VBA?

Thank you

[daddylonglegs]

If A2:A10 is fully populated with numbers (some of which may be zero) then this array formula will give you one of the non-zero values, randomly. If there are repeated values then those values will be more likely to appear

=INDEX(A2:A100,SMALL(IF(A2:A10<>0,ROW(A2:A10)-ROW(A2)+1),INT(RAND()*COUNTIF(A2:A10,"<>0")+1)))

confirm with CTRL+SHIFT+ENTER

[FDibbins]

Maybe try something like this...

	R	S	T	U
27	41.41	8		65.95
28	178.27	1
29	52.61	7
30	65.95	6
31	75.33	3
32	69.98	5
33	136.53	2
34	73.32	4
35	13.2	9
36	0	10
37	0	10
38	0	10

S27=RANK(R27,$R$27:$R$38)
copied down
U27=INDEX(R27:R38,MATCH(RANDBETWEEN(1,MAX(S27:S38)),S27:S38,0))

[AlKey]

Here is a formula that will generate random numbers without duplicates or zeros

In A2 and copy down. In this example formula will generate numbers from 1 through 15. To increase the quantity of numbers change 15 to the desired number.

=LARGE(ROW($1:$15)*NOT(COUNTIF($A$1:A1,ROW($1:$15))),RANDBETWEEN(1,(15+2-1)-ROWS(A$1:A1)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A
2	8
3	10
4	9
5	12
6	6
7	2
8	3
9	5
10	15
11	14
12	4
13	1
14	13
15	11
16	7

[junk87]

cheers guys. sorted now