Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Random with Number And Probability [very long]

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 837
Default Random with Number And Probability [very long]

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Random with Number And Probability [very long]


"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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Random with Number And Probability [very long]

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Random with Number And Probability [very long]


"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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Random with Number And Probability [very long]

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Random with Number And Probability [very long]


"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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Random with Number And Probability [very long]

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Random with Number And Probability [very long]


"Tom Ogilvy" ha scritto nel messaggio
...
news://msnews.microsoft.com/microsof...t.office.excel

--
Regards,
Tom Ogilvy


i try
tk
by !


  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Random with Number And Probability [very long]


"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Generate random sequence based on probability James R[_2_] Excel Discussion (Misc queries) 5 December 19th 09 01:46 PM
Probability of random numbers Soccer boy[_2_] Excel Discussion (Misc queries) 1 January 21st 09 05:40 PM
Column picked randomly with probability relative to number of entr Neil Goldwasser Excel Worksheet Functions 4 May 30th 06 08:55 AM
How do I find random number in list of random alpha? (Position is. jlahealth-partners Excel Discussion (Misc queries) 0 February 8th 05 05:31 PM
Selecting at random with weighted probability Damage Excel Worksheet Functions 2 January 31st 05 11:06 PM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"