Random numbers function

M

momma

I am trying to make a sheet with random numbers in 100 cells from 0 to 99 and
not duplicate any number. Does anyone know what function I need to enter to
do this?
 
M

momma

momma said:
I am trying to make a sheet with random numbers in 100 cells from 0 to 99 and
not duplicate any number. Does anyone know what function I need to enter to
do this?
Click to expand...

Thanks for the info but I still can't seem to do what I want.

I might not have explained this properly. Let me try again. I have a range
of 10 rows by 10 columns. I want the computer to randomly enter the numbers
from 0 to 99 in each cell (one number per cell) without any duplication. I
am somewhat ignorant when it comes to entering formulas so the simplist way
would be great.
 
D

daddylonglegs

Here's a way to do this with formulas - you need one helper column

To get your numbers 0-99 with no repeats in A1:J10

use this formula in L1 and copy down to L100
=RAND()

Put this formula in A1 and copy across to J1 and down all 10 columns to
row 10
=RANK(INDEX($L$1:$L$100,ROW()+10*(COLUMN()-1)),$L$1:$L$100)-1

hide column L if you wish

random numbers are regenerated every time sheet is recalculated (hit
F9)
 
B

Ben-gr6teacher

I've used =ROUND(RAND()*(6-1)+1,0) in 1000 cells to simulate rolling 1000
dice. This consistently has a bias toward generating 2, 3, 4, or 5 about 20%
(more than 16.6%) of the time and 1 or 6 only 10% of the time (less than
16.6%). When I used RANDBETWEEN(1,6) I do not have this problem.

Anyone know why?
 
D

Dana DeLouis

Hi. I think that with the use of Round, numbers in the middle get a full
unit to work with.
Meaning that say 3.5-4.5 will round to 4.
However, the smallest number 0 occurs half as often... only with numbers
0-0.5. You need a whole range of say 0-1.
See if using Int will work.

=INT(RAND()*6)+1
 
V

vandenberg p

Hello:

Think about it this way. Since Rand() produces random numbers between 0 and 1. The 6
outcomes can be calculated by using random numbers from 0 to .1666666666..... to represent
one, and all numbers from .166666666.... to .3333333333 to represent two etc. until
..83333333..... to 1.000000 to represent six. Your formula will result in .1 being
represented as a two and numbers less than .1 being one. Hence about 10% of numbers will be
one. This same process produces extra two, three, four and five. And again produces less
sixes, any random between number between .8333333 and .9000 will produce five rather than
6, again that would mean about 10% sixes, since the numbers between .83 and .90 do not
produce the required six.

Try this formula: =INT((6*RAND())+1) (this is from the Excel help file)

It will produce the results you want.

Pieter Vandenberg


: I've used =ROUND(RAND()*(6-1)+1,0) in 1000 cells to simulate rolling 1000
: dice. This consistently has a bias toward generating 2, 3, 4, or 5 about 20%
: (more than 16.6%) of the time and 1 or 6 only 10% of the time (less than
: 16.6%). When I used RANDBETWEEN(1,6) I do not have this problem.

: Anyone know why?
 
B

Ben-gr6teacher

THANK YOU, Dana!

Dana DeLouis said:
Hi. I think that with the use of Round, numbers in the middle get a full
unit to work with.
Meaning that say 3.5-4.5 will round to 4.
However, the smallest number 0 occurs half as often... only with numbers
0-0.5. You need a whole range of say 0-1.
See if using Int will work.

=INT(RAND()*6)+1
Click to expand...
 
Top