3

I was referring to this post on generating random x numbers using =RAND(), however it cater for every number including those with decimal. I want to generate only positive whole number (e.g. 1, 3, 50) and not those with decimal.

To be clear, for example, I want to generate:

50 random positive whole numbers that has the sum of 1000

PS: If you find this question for Excel solution redundant, let me know and I'll close this.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
Dan
  • 810
  • 2
  • 11
  • 29

1 Answers1

4

I offer a solution which has better statistical properties than I had originally supposed:

Estimate the upper limit of each number as being twice the mean. In your case the mean is 20 (1000 / 50), so the upper limit is 39, as the lower limit is 1.

  1. Generate 50 floating point numbers using

    =RAND() * 38 + 1

  2. Sum the total that you get, call that s

  3. Rescale each number by multiplying by 1000 / s, and round the result in the normal way. (Use ROUND.)

  4. Sum that. Call it t.

  5. If t is less than 1000, add 1000 - t to the smallest number. If it's greater than 1000, subtract t - 1000 from the largest number.

This should be approximately uniformly distributed and have a good mean. You can run the results through some statistical tests for randomness to gauge whether or not it will fit your requirements. My instinct suggests to me that it will not be much worse than Rand() itself.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • should step 1 be limited to whole numbers? – ashleedawg Nov 29 '17 at 08:26
  • Absolutely not. You don't round them until the third step. Otherwise the scaling will not work very well. – Bathsheba Nov 29 '17 at 08:27
  • 1
    Cool question. Cool answer. Now I'm gonna be messing with this all night. :-) – ashleedawg Nov 29 '17 at 08:28
  • Okay, my smallest number was 0 so that became 3. Was 0 allowable? (original random number 0.058191422) – ashleedawg Nov 29 '17 at 08:32
  • @ashleedawg: Oops. Didn't read the small print. You need to adjust (1). Thanks for pointing that out. Let's publish jointly! – Bathsheba Nov 29 '17 at 08:33
  • Haha I would have believed it was my mistake if it wasn't for the "edit history"! – ashleedawg Nov 29 '17 at 08:35
  • Can you suggest an example of where this would be used in statistics? I consider myself a data/stats geek (no degrees or anything like that, mainly business metrics and stuff like that. What should i look up on Wikipedia to learn more? :-) – ashleedawg Nov 29 '17 at 08:39
  • Try "Generating random numbers subject to summation constraints". Deep down, my method works well since `Rand()` is a *linear congruential generator*, so step (3) doesn't really change the properties too much. Note that step (5) reduces the degrees of freedom to the correct value (49) with minimal impact on the mean and variance of the sample. – Bathsheba Nov 29 '17 at 08:40
  • I like this! One small suggestion - in step 1 the upper limit is 40 and lower is 1 which means that average is 20.5 - wouldn’t it be better if the average was 20? – barry houdini Nov 29 '17 at 08:50
  • @barryhoudini: Indeed, yes I would have been out on the 1st moment. Oops. Changed, with thanks. – Bathsheba Nov 29 '17 at 08:51
  • uhh, brackets on #1? :-) – ashleedawg Nov 29 '17 at 09:05
  • @ashleedawg: Nope, the +1 is added to the final result. – Bathsheba Nov 29 '17 at 09:06
  • 1
    @Bathsheba - gotcha. And thank you for the suggestions. And my 13yr old's math word of the day will be... _**congruential**_ (He's in Grade 8 doing the stuff I learned in Grade 12 math so it's getting tough to challenge him!) – ashleedawg Nov 29 '17 at 09:13
  • Can someone explain what the origins of the magic number `38` are? – Qqwy Aug 14 '19 at 13:08