Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi from Italy......so sorry for my bad english.....
As you Know in Excel there is a function called ATPVBAEN.XLA , that generate a casual combination : here there is the excel example vba code : Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$P$2:$W$5000"), 8, 5000 _ , 7, , ActiveSheet.Range("$AI$1:$AJ$21") and in the example above if you have this in the range A1:j:21 : 1 0,035181345 2 0,037730718 3 0,045967154 4 0,0300826 5 0,03953489 6 0,038750467 7 0,025493729 8 0,032122098 9 0,025689834 10 0,030592474 11 0,027533227 12 0,02765089 13 0,030514032 14 0,03184755 15 0,028945187 16 0,025493729 17 0,031925992 18 0,0300826 19 0,032122098 20 0,032292056 Excel return, this : 2 3 8 8 12 18 19 20 2 2 2 4 4 5 5 18 6 7 7 7 8 10 12 19 2 6 6 9 17 20 20 20 6 7 10 15 16 16 17 20 3 3 5 7 8 10 11 20 2 2 3 5 6 14 16 19 the problem is that in every rows there are repeat number :( i don't want REPEAT number ...... can I solve it with VBA ???? TIA ! |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
The standard way to avoid repeated numbers in a selection of random
integers is to list all of the integers that might potentially be drawn in one column, put =RAND() in the adjacent column, sort both by the RAND column, and read off however many integers you wish in order. You could program this in VBA. Jerry Aristotele64 wrote: Hi from Italy......so sorry for my bad english..... As you Know in Excel there is a function called ATPVBAEN.XLA , that generate a casual combination : here there is the excel example vba code : Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$P$2:$W$5000"), 8, 5000 _ , 7, , ActiveSheet.Range("$AI$1:$AJ$21") and in the example above if you have this in the range A1:j:21 : 1 0,035181345 2 0,037730718 3 0,045967154 4 0,0300826 5 0,03953489 6 0,038750467 7 0,025493729 8 0,032122098 9 0,025689834 10 0,030592474 11 0,027533227 12 0,02765089 13 0,030514032 14 0,03184755 15 0,028945187 16 0,025493729 17 0,031925992 18 0,0300826 19 0,032122098 20 0,032292056 Excel return, this : 2 3 8 8 12 18 19 20 2 2 2 4 4 5 5 18 6 7 7 7 8 10 12 19 2 6 6 9 17 20 20 20 6 7 10 15 16 16 17 20 3 3 5 7 8 10 11 20 2 2 3 5 6 14 16 19 the problem is that in every rows there are repeat number :( i don't want REPEAT number ...... can I solve it with VBA ???? |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() "Jerry W. Lewis" ha scritto nel messaggio ... The standard way to avoid repeated numbers in a selection of random integers is to list all of the integers that might potentially be drawn in one column, put =RAND() in the adjacent column, sort both by the RAND column, and read off however many integers you wish in order. You could program this in VBA. hi......many thanks the problem that i don't konw the vba code as well to do it :( by ! |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Your probabilities add up to approximately 0,63955267
the probabilities of a probability distribution should add up to 1,0 do you want to scale your probabilities on the basis of 0,63955267 or what is it you actually want to do Your input range is 21 rows, but you only show data for 20. Is it your intent that 21 has a probability of 1 - 0,63955267 -- Regards, Tom Ogilvy "Aristotele64" wrote in message ... "Jerry W. Lewis" ha scritto nel messaggio ... The standard way to avoid repeated numbers in a selection of random integers is to list all of the integers that might potentially be drawn in one column, put =RAND() in the adjacent column, sort both by the RAND column, and read off however many integers you wish in order. You could program this in VBA. hi......many thanks the problem that i don't konw the vba code as well to do it :( by ! |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() "Tom Ogilvy" ha scritto nel messaggio ... Your probabilities add up to approximately 0,63955267 the probabilities of a probability distribution should add up to 1,0 do you want to scale your probabilities on the basis of 0,63955267 or what is it you actually want to do Your input range is 21 rows, but you only show data for 20. Is it your intent that 21 has a probability of 1 - 0,63955267 i take only 20 rows for not waste space .....this an example correct : 1 0,02553976 2 0,03241585 3 0,036420605 4 0,036949535 5 0,042767764 6 0,033398148 7 0,028486656 8 0,030451253 9 0,02501083 10 0,040274238 11 0,030451253 12 0,03082906 13 0,025237515 14 0,028562217 15 0,028562217 16 0,022592865 17 0,041861027 18 0,039291939 19 0,042843326 20 0,029141522 21 0,02593268 22 0,031630011 23 0,03032028 24 0,034838853 25 0,022920298 26 0,026587545 27 0,027242411 28 0,034838853 29 0,028486656 30 0,032088417 31 0,025605247 32 0,028421169 |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
This is substantively a repeat of the same posting you did on 11 April 2004
Subject: ATPVBAEN XLA [Number casual] Jerry gave you several suggestions then. Explain why these will not work and what is new about your question to avoid wasting time rehashing/revisiting the same old information. The fact that the Random tool in the Analysis toolpak does not generate unique random numbers was pretty well established. There is no setting or argument that changes that. -- Regards, Tom Ogilvy "Aristotele64" wrote in message ... Hi from Italy......so sorry for my bad english..... As you Know in Excel there is a function called ATPVBAEN.XLA , that generate a casual combination : here there is the excel example vba code : Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$P$2:$W$5000"), 8, 5000 _ , 7, , ActiveSheet.Range("$AI$1:$AJ$21") and in the example above if you have this in the range A1:j:21 : 1 0,035181345 2 0,037730718 3 0,045967154 4 0,0300826 5 0,03953489 6 0,038750467 7 0,025493729 8 0,032122098 9 0,025689834 10 0,030592474 11 0,027533227 12 0,02765089 13 0,030514032 14 0,03184755 15 0,028945187 16 0,025493729 17 0,031925992 18 0,0300826 19 0,032122098 20 0,032292056 Excel return, this : 2 3 8 8 12 18 19 20 2 2 2 4 4 5 5 18 6 7 7 7 8 10 12 19 2 6 6 9 17 20 20 20 6 7 10 15 16 16 17 20 3 3 5 7 8 10 11 20 2 2 3 5 6 14 16 19 the problem is that in every rows there are repeat number :( i don't want REPEAT number ...... can I solve it with VBA ???? TIA ! |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() "Tom Ogilvy" ha scritto nel messaggio ... This is substantively a repeat of the same posting you did on 11 April 2004 Subject: ATPVBAEN XLA [Number casual] Jerry gave you several suggestions then. Explain why these will not work and what is new about your question to avoid wasting time rehashing/revisiting the same old information. first I don't understand english ver well...... the reply that i have in my first post was not clear for me.... my number are all : "that might potentially be drawn" The fact that the Random tool in the Analysis toolpak does not generate unique random numbers was pretty well established. There is no setting or argument that changes that. ok.... i search BEYOND ATPVBAEN XLA a vba solution......if exist by ! |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
news://msnews.microsoft.com/microsof...t.office.excel
-- Regards, Tom Ogilvy "Aristotele64" wrote in message ... "Tom Ogilvy" ha scritto nel messaggio ... This is substantively a repeat of the same posting you did on 11 April 2004 Subject: ATPVBAEN XLA [Number casual] Jerry gave you several suggestions then. Explain why these will not work and what is new about your question to avoid wasting time rehashing/revisiting the same old information. first I don't understand english ver well...... the reply that i have in my first post was not clear for me.... my number are all : "that might potentially be drawn" The fact that the Random tool in the Analysis toolpak does not generate unique random numbers was pretty well established. There is no setting or argument that changes that. ok.... i search BEYOND ATPVBAEN XLA a vba solution......if exist by ! |
#9
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() "Tom Ogilvy" ha scritto nel messaggio ... news://msnews.microsoft.com/microsof...t.office.excel -- Regards, Tom Ogilvy i try tk by ! |
#10
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() "Aristotele64" ha scritto nel messaggio ... "Tom Ogilvy" ha scritto nel messaggio ... news://msnews.microsoft.com/microsof...t.office.excel -- Regards, Tom Ogilvy oopps italian newsgroup ? no thanks.....we don't use excel as yankies |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Generate random sequence based on probability | Excel Discussion (Misc queries) | |||
Probability of random numbers | Excel Discussion (Misc queries) | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) | |||
Selecting at random with weighted probability | Excel Worksheet Functions |