• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Random numbers within a range - But range has non-contiguous numbers

Status
Not open for further replies.
J

Jignesh Pandya

New Member
I need to draw a sample from population of random numbers. The numbers are in the range of say 10001 to 20000. But in that range also not all numbers are included - Thus, for example, the population range can be [10002, 10005, 10010, 10013, ....., 19993, 19996, 19999] with certain numbers skipped in the range. That population of numbers will be in the form of a csv file. Out of such a population range, I need to draw a sample of say 1000 random numbers (non repeating). Is there any formula in excel / power query or may be defined custom function in power query which can do that? I can easily use data science language like Python to achieve the result, but am curious about excel options. I tried to look through some functions like random(), randarray(), etc. but they give random numbers from anywhere - not within a particular range. Randbetween function does that but it requires complicated combination of other functions along with it to ensure that random numbers are not duplicated and also, randbetween() draws from a contiguous range not like the one I cited above.
 
I got the answer from other source. Inconvenience is highly regretted. Please close the thread. Actually, the range of random numbers to be generated is more than 1,000,000 out of more than 10,000,000 numbers and since excel has rows limitation up to about 1,048,576 - this cannot be handled in excel.
 
Last edited:
Jignesh Pandya
Please reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org

Q: Your The numbers are in the range of say 10001 to 20000.
Your I need to draw a sample of say 1000 random numbers (non repeating).
... but You meant a bit more - was there any reason for that?
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
Q: Why should use only one column for those random numbers?
... sample of expected results - would help to get replies too.
... did You expect to get a .csv-file?

Your I got the answer from other source. ... did You mean about cross-posting?
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Hello vletm

Sorry about my mistakes in posting and others which you have pointed out like cross posting.

The issue, as I mentioned was that excel could not handle the volume of data of more than 10,000,000 rows so I had to close the issue prematurely. The solution I got from other source was also not in excel so there was no point in posting it here as this is an excel specific forum.

Secondly, I tried some solutions myself using randarray function combined with rank in excel to get non repeating random numbers on the cross section of the population taking only 100,000 rows as population (out of 10,000,000) but even with that small amount of data, the formula just got hanged / stuck - So there was no point venturing further into excel for my use case. Therefore I requested to close the thread expressing my regrets.

Hope, I have clarified my position and logic behind pre-mature closure of the thread. However, my apologies for any rules broken due to my not being aware about them.

Kind regards and wish you the best,
 
Jignesh Pandya
You seems to skip my 2nd question...
Newer version Excel can have 16 384 columns ... means 17'179'869'184 cells per sheet. It should be enough for You too.
Without a sample expected results - it's a challenge to offer it.

About Forum Rules ... my only idea is that those are meant to follow.

Take care.

Jignesh Pandya requested to close this thread.
 
Status
Not open for further replies.
Back
Top