0

Problem statement...

With regards to Google Sheets, I'm currently looking to generate a randomized fixed quantity of numbers between two known values, that when added... equal a known value.

So let's pretend that I wanted 10 random values that when added... equal 100. But each of the values have to be between 8 and 12 and in a single column. So the output looks like...

8 9 12 11 8 9 11 11 9 12

How would setup a formula in GSheets or Excel to output this? Where I can enter the quantity of numbers needed and desired final total? You would be God send to solve this, thank you!

Judd
  • 3
  • 1

1 Answers1

0

Think of your problem as selecting balls from a bag without replacement. There are 120 balls, 12 each of 10 different colors. Set aside 8 of each color. Put the remaining 40 in a bag and randomly select 20 without replacement, and place them with the other 80. Now you have 100 balls, with a random value between 8 and 12 of each color. The resulting numbers will not be uniformly distributed, however. Search on "sampling without replacement" to get the formula for the distribution.

For your specific example, put this into A1:A10:

=FREQUENCY(INT((ROUND(MOD(SMALL(RANDBETWEEN(-1e12,SIGN(ROW(INDIRECT("1:40")))*1e12)+ROW(INDIRECT("1:40"))/100,ROW(INDIRECT("1:20"))),1),2)*100-1)/4)+1,ROW(INDIRECT("1:10")))+8

Commit with Ctrl+Shift+Enter.

To generalize it, I set up my spreadsheet like this:

  • B1: the number of random numbers desired (e.g., 10)
  • B2: the minimum (e.g., 8)
  • B3: the maximum (e.g., 12)
  • B4: the desired total (e.g., 100)
  • B5: =(B3-B2)*B1 (the number of slots available)
  • B6: =CEILING.MATH(LOG10((B3-B2)*B1))
  • B7: =10^B6
  • B8: =B4-B1*B2 (the number of slots to fill)

And the generalized formula is:

=FREQUENCY(INT((ROUND(MOD(SMALL(RANDBETWEEN(-1e12,SIGN(ROW(INDIRECT("1:"&B5)))*1e12)+ROW(INDIRECT("1:"&B5))/B7,ROW(INDIRECT("1:"&B8))),1),B6)*B7-1)/(B3-B2))+1,ROW(INDIRECT("1:"&B1)))+B2

Recalculate the sheet to get a new set of numbers (F9 in Excel).

jblood94
  • 10,340
  • 1
  • 10
  • 15