Random numbers function

[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?

[Niek Otten]

http://www.tushar-mehta.com/excel/ne...ion/index.html

--
Kind regards,

Niek Otten

"momma" <[email protected]> wrote in message
news:[email protected]...
>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?

[momma]

"momma" wrote:

> 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?

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.

[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)

[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?

[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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Ben-gr6teacher" <[email protected]> wrote in message
news:[email protected]...
> 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?

[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


Ben-gr6teacher <[email protected]> wrote:
: 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?

[Ben-gr6teacher]

THANK YOU, Dana!

"Dana DeLouis" wrote:

> 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
>
> --
> HTH. :>)
> Dana DeLouis
> Windows XP, Office 2003
>
>
> "Ben-gr6teacher" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Ben-gr6teacher]

Thanks, Pieter!!!

"vandenberg p" wrote:

> 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
>
>
> Ben-gr6teacher <[email protected]> wrote:
> : 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?
>