Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help for RAND
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? -- jinvictor ------------------------------------------------------------------------ jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099 View this thread: http://www.excelforum.com/showthread...hreadid=539613 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help for RAND
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 -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=539613 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help for RAND
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help for RAND
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|