×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

sorting 100 000 random numbers in ONE column (excel)

sorting 100 000 random numbers in ONE column (excel)

sorting 100 000 random numbers in ONE column (excel)

(OP)
Hello
I need 100 000 random numbers, following a normal distribution. I tried using excel, tools, generate random numbers... the problem is: it works for 30 000, but not much more than that. I tried doing 4 times 25  000 numbers. that works, but i then  need to sort all the numbers, and i cant paste all the results in ONE column. The max seems to be around 60 000 or so.. Is that excel's limit? Is there any other way to do it?
thanks

RE: sorting 100 000 random numbers in ONE column (excel)

It's limited to 65,536 rows by 256 columns, at least in the versions I am familiar with (type limitations into help)

Use VB, generate them and sort them into two columns, one less than 0.5, then cut and paste them into whatever analysis program.

That's a fairly cumbersome approach, you'd be better off using a better stats package, R, scilab, octave are all free and will handle massive amounts of data and I am sure theyll generate suitable random numbers.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: sorting 100 000 random numbers in ONE column (excel)

If you sort random numbers, are they really random?

RE: sorting 100 000 random numbers in ONE column (excel)

It sounds like you are setting up a Monte Carlo simulation.  As GregLocock as said there are much better applications for this out there.  My favourite is Crystal Ball (Decisioneering).

...Or you could wait for Excel 2007 which will have plenty more than 100,000 rows. big smile

RE: sorting 100 000 random numbers in ONE column (excel)

Mint... yes, but they are sorted pseudo random numbers... <G>

Dik

RE: sorting 100 000 random numbers in ONE column (excel)

(OP)
Thanks for the input everyone
it is in fact a montecarlo simulation.. i'll try and look into other applications. just hope i can figure out how to use them properly
purple smile

RE: sorting 100 000 random numbers in ONE column (excel)

Would 65,536 runs of a Monte Carlo simulation really be all THAT much worse than 100,000 runs?! (Depends on exactly what you are simulating, I suppose.)

If so, perhaps you could limit your spreadsheet to 50,000 rows, and run it twice?

RE: sorting 100 000 random numbers in ONE column (excel)

Oh, good, another cynic. My first thought was how much data do you have that supports a normal distribution to that many samples? I don't think I've seen any engineering data that could really support using a normsl random number generator more sophisticated than x=0.2*(rnd(1)+rnd(1)+rnd(1)+rnd(1)+rnd(1))

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: sorting 100 000 random numbers in ONE column (excel)

but 100,000 runs sounds REALLY thorough.

TTFN



RE: sorting 100 000 random numbers in ONE column (excel)

This is confusing me.  For the size data you are after, and the fact you want them sorted, it seems you should just generate the numbers from the normal distribution equation.  I know that sounds heretical, but what exactly does a monte carlo simulation of a huge block of sorted data prove?  Can you not just compute the failure threshold value and then back calculate the % failure from your normal distribution equation?  I must be missing something.

RE: sorting 100 000 random numbers in ONE column (excel)

I think Office 2007 has expanded the limits of Excel and will suit your needs.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources