Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random numbers function
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random numbers function
http://www.tushar-mehta.com/excel/ne...ion/index.html
-- Kind regards, Niek Otten "momma" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random numbers function
"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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random numbers function
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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=508266 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bias in Random Numbers
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bias in Random Numbers
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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bias in Random Numbers
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 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bias in Random Numbers
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" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bias in Random Numbers
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 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Numbers | Excel Worksheet Functions | |||
Generation of random numbers and sum of those with a condition | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Validating random numbers | Excel Worksheet Functions | |||
Random Numbers Change? | Excel Worksheet Functions |