sorting 100 000 random numbers in ONE column (excel)

[shanvan]

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

 

[GregLocock]

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.

 

[MintJulep]

If you sort random numbers, are they really random?

 

[cummings54]

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. :-D

 

[dik]

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

Dik

 

[shanvan]

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
:-s

 

[jhardy1]

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?

 

[GregLocock]

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.

 

[IRstuff]

but 100,000 runs sounds REALLY thorough.

TTFN

 

[Dinosaur]

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.

 

[IFRs]

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