help for RAND

[jinvictor]

when i use RAND() to choose no between 1-15, every time i do it, no changes, how to stop that happen?
Example: A1-1,A2-3,A3-12,but when i do A2, A1 change to different no.

and what can i do if i want to pick random no between 1-15 but i dont want 8 to show up?

what can i do if i want a1-a140 showing random no between 1-15, but every no show up equally?

[CaptainQuattro]

when i use RAND() to choose no between 1-15, every time i do it, no changes, how to stop that happen?
Example: A1-1,A2-3,A3-12,but when i do A2, A1 change to different no.

and what can i do if i want to pick random no between 1-15 but i dont want 8 to show up?

what can i do if i want a1-a140 showing random no between 1-15, but every no show up equally?

To stop number in A1 from changing, enter formula, and press F9 before pressing enter.

To elimate "8's": try formula

=IF(RAND()*(15-1)+1=8,RAND()*(7-1)+1,RAND()*(15-1)+1)

To produce same random number in a cells A1 TO A140 enter rand()... formula in cell A1 and make formula in cells A2 to A140 = $A$1

[David J. Braden]

Your "else" in the IF can still lead to an 8.
This one's easy, because the 8 is smack in the middle if the set. So,
assuming he wants integers,
=IF(Rand()<0.5,RANDBETWEEN(1,7),RANDBETWEEN(9,15))
or
=IF(Rand()<0.5,0,8)) + RANDBETWEEN(1,7)
which is faster and cleaner.

For doubles,
=IF(Rand()<0.5,1,8+eps)+RAND()*(7-eps)
where eps is a very small number, say, 10E-300.
The eps is added in to avoid a possible (depending on the version of
Excel) 0.

HTH
Dave Braden

CaptainQuattro wrote:
> jinvictor Wrote:
>> when i use RAND() to choose no between 1-15, every time i do it, no
>> changes, how to stop that happen?
>> Example: A1-1,A2-3,A3-12,but when i do A2, A1 change to different no.
>>
>> and what can i do if i want to pick random no between 1-15 but i dont
>> want 8 to show up?
>>
>> what can i do if i want a1-a140 showing random no between 1-15, but
>> every no show up equally?
>
> To stop number in A1 from changing, enter formula, and press F9 before
> pressing enter.
>
> To elimate "8's": try formula
>
> =IF(RAND()*(15-1)+1=8,RAND()*(7-1)+1,RAND()*(15-1)+1)
>
> To produce same random number in a cells A1 TO A140 enter rand()...
> formula in cell A1 and make formula in cells A2 to A140 = $A$1
>
>

--
Please keep response(s) solely within this thread.

[[email protected]]

Hello,

I suggest to take my function UniqRandInt from
http://www.sulprobil.com/html/uniqrandint.html and to array-enter in
B1:B140:
=UniqRandInt(14,10)
(Select B1:B140, enter the formula above and hit CTRL + SHIFT + ENTER)
(This creates random numbers 1 to 14, each one exactly 10 times)

Then enter into A1:
=IF(B1=8,15,B1)
and copy this down to A140.
(Now eights will become 15)

HTH,
Bernd