Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN XLA [Number casual]
Hi from italy...so sorry for my bad english :)
I use this code : Application.Run "ATPVBAEN.XLA!Histogram", Sheets("Archivio").Range("$A$2:$H$5000") _ , ActiveSheet.Range("$D$46:$E$77"), ActiveSheet.Range("$A$1:$A$32"), False _ , False, False, False to generate a serial of casual number. My question : is possibile to *avoid* that a number is present 2 o 3 time in a row ? example to avoid : 9 10 11 11 12 15 18 30 6 11 11 15 23 23 25 31 TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN XLA [Number casual]
I am not sure how a histogram generates a serial of casual number, but if
you mean randombetween, then it does not provide unique numbers. -- Regards, Tom Ogilvy "Aristotele64" wrote in message ... Hi from italy...so sorry for my bad english :) I use this code : Application.Run "ATPVBAEN.XLA!Histogram", Sheets("Archivio").Range("$A$2:$H$5000") _ , ActiveSheet.Range("$D$46:$E$77"), ActiveSheet.Range("$A$1:$A$32"), False _ , False, False, False to generate a serial of casual number. My question : is possibile to *avoid* that a number is present 2 o 3 time in a row ? example to avoid : 9 10 11 11 12 15 18 30 6 11 11 15 23 23 25 31 TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN XLA [Number casual]
As Tom has noted, random integers returned by RANDBETWEEN are not
constrained to be unique. The usual approach to generate unique random integers between say 1 and 50, would be to put all the integers 1..50 in 50 rows of a column; put =RAND() in the corresponding rows of the adjacent column; sort both columns by the random column; then use however many values you want, in order, from the first column. Jerry Aristotele64 wrote: Hi from italy...so sorry for my bad english :) I use this code : Application.Run "ATPVBAEN.XLA!Histogram", Sheets("Archivio").Range("$A$2:$H$5000") _ , ActiveSheet.Range("$D$46:$E$77"), ActiveSheet.Range("$A$1:$A$32"), False _ , False, False, False to generate a serial of casual number. My question : is possibile to *avoid* that a number is present 2 o 3 time in a row ? example to avoid : 9 10 11 11 12 15 18 30 6 11 11 15 23 23 25 31 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN XLA [Number casual]
"Tom Ogilvy" ha scritto nel messaggio ... I am not sure how a histogram generates a serial of casual number, but if you mean randombetween, then it does not provide unique numbers. sorry i have wrong to copy cody , this is my code : Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$P$2:$W$5000"), 8 _ , 5000, 7, , ActiveSheet.Range("$G$1:$H$32") Is possible to write a function to generate a similar ATPVBAEN.XLA option ? tk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN XLA [Number casual]
"Jerry W. Lewis" ha scritto nel messaggio ... As Tom has noted, random integers returned by RANDBETWEEN are not constrained to be unique. The usual approach to generate unique random integers between say 1 and 50, would be to put all the integers 1..50 in 50 rows of a column; put =RAND() in the corresponding rows of the adjacent column; sort both columns by the random column; then use however many values you want, in order, from the first column. many thanks i don't know this......... but my problem is that i want generate casual number with a column of number and respective column of a probability and ATPVBAEN XLA was perfect , only things bad is that a row have number not unique Example i have this : Num Prob 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 21 0,0292668 22 0,032088106 23 0,030116591 24 0,034263571 25 0,028552976 26 0,028077093 27 0,024643938 28 0,026173561 29 0,029980625 30 0,032835922 31 0,032088106 32 0,032360039 this code : Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$P$2:$W$5000"), 8 _ , 5000, 7, , ActiveSheet.Range("$G$1:$H$32") working but on 5000 rows i have 3000 with a repeat number..... is there on web page where speaking : how to write ATPVBAEN.XLA!Random function ? TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN XLA [Number casual]
My guess is that you want to generate 5000 random samples of 8
observations from a custom specified discrete distribution. Each sample of 8 is to be drawn without replacement. A very painful way to do this would be to remove the final argument from your ATPVBAEN.XLA!Random call. That would return 5000 random samples of 8 from the Uniform(0,1) distribution. For each random sample [row of U(0,1) random variables] proceed as follows: If the first uniform random number <0,035181345 then the first random number from your distribution is 1. If the first uniform random number =0,035181345 but <(0,035181345+0,037730718) then the first random number from your distribution is 2 ... Create a new custom distribution for only 31 values (excluding the one already drawn) by rescaling the remaining probabilities to sum to 1. Map the second uniform random number to this reduced custom distribution. Continue reducing and choosing until all 8 random numbers have been drawn. Repeat for the next sample. You could automate this process with a macro, but I do not immediately see an elegant approach. Jerry Aristotele64 wrote: .... but my problem is that i want generate casual number with a column of number and respective column of a probability and ATPVBAEN XLA was perfect , only things bad is that a row have number not unique Example i have this : Num Prob 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 21 0,0292668 22 0,032088106 23 0,030116591 24 0,034263571 25 0,028552976 26 0,028077093 27 0,024643938 28 0,026173561 29 0,029980625 30 0,032835922 31 0,032088106 32 0,032360039 this code : Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$P$2:$W$5000"), 8 _ , 5000, 7, , ActiveSheet.Range("$G$1:$H$32") working but on 5000 rows i have 3000 with a repeat number..... is there on web page where speaking : how to write ATPVBAEN.XLA!Random function ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
atpvbaen.xla cound not be found | Excel Discussion (Misc queries) | |||
how do i set up wage slips for my casual employees | New Users to Excel | |||
Atpvbaen.xls | Excel Worksheet Functions | |||
minimum for casual cells but 0 | Excel Discussion (Misc queries) | |||
ATPVBAEN.XLA!Random Call in Macro | Excel Programming |