Random numbers

[Emma007]

Hello All,

I need to generate random numbers up to the "Count of ID" that will equal the amount indicated in the 3rd column. I need help.

Month	Count of ID	Amount
JAN	30	525,050.01
FEB	26	373,365.10
MAR	26	454,834.22
APR	26	372,316.54
MAY	37	254,985.76
JUN	33	191,889.21
JUL	34	182,674.81
AUG	31	157,652.11
SEP	37	127,869.98
OCT	47	112,130.36
NOV	39	141,826.33
DEC	41	148,695.32

[KamenRider]

Hi @Emma007 ,

Can you please give an example or illustration? you are saying that Count of ID with equal Amount?

Kamen

[Emma007]

Hi @kamenrider,

So take January for eg. I want 30 generated rows with random numbers that add up to 525,050.01

[apathetichell]

so this is a batch macro -> iteratice macro process. Which is rare. because almost nothing is actually best done with an iterative macro. do you have any rules with the initial random amounts (ie they should be no more or not less than X % of the total) - asking because the way how randomness works, random number 1 could be 90% of the total- and you may want to some kind of mean/standard deviation limits on the random number.

[apathetichell · Accepted solution]

Here's the iterative macro logic that I'd use. You'd probably want to turn this into a batch macro - or add a record id prior to the iterative macro.

[Emma007]

Super Great!! Thanks so much. Super grateful

[apathetichell]

Great to hear @Emma007 - can you mark my post as a solution?

[CoG · Accepted solution]

Here is a macro-free solution. This is actually a complex issue because how to handle randomness is not straight-forward. Fundamentally, you can just generate random numbers for all [Count of ID] records, and then normalize the results to the target sum:

I rounded to 2 decimal places, so there is technically a slight bit of non-randomness involved, which is the allotment/removal of remaining/extra decimals to the first record of each group. 

Hope this helps and Happy Solving!

[Emma007]

I am deeply grateful for your support.  Thank you so much.