Generating random numbers to obtain a fixed sum in column

[fatoom91]

Hi I'm trying to generate random number in column B for each userID that total sum for each userID is in column E,
and if there is an order canceled in column D it will still blank ,
but I didn't know how to do it, my code is here:

Please Login or Register  to view this content.

my attachement the first sheet of my code, and the second sheet what I really except

[fatoom91]

can any one help me for this issue

[MrShorty]

Are you required to use a macro for this? Here's how I did it using spreadsheet functions:

1) In G2, generate a random number or 0 if canceled =IF(D2="order canceled",0,RAND()) and copy down.
2) In H2, sum up the random numbers for each userid =SUMIFS($G$2:$G$28,$A$2:$A$28,A2) and copy down.
3) In I2, compute the random numbers =G2/H2*VLOOKUP(A2,$A$2:$E$28,5,FALSE).

That will generate random reals that add up to your values. If you need random integers, then you can round the results to the nearest integer, adding or subtracting from one or more of the random numbers to make sure they all add up to the chosen value.

Will that work for you?

[fatoom91]

Are you required to use a macro for this? Here's how I did it using spreadsheet functions:

1) In G2, generate a random number or 0 if canceled =IF(D2="order canceled",0,RAND()) and copy down.
2) In H2, sum up the random numbers for each userid =SUMIFS($G$2:$G$28,$A$2:$A$28,A2) and copy down.
3) In I2, compute the random numbers =G2/H2*VLOOKUP(A2,$A$2:$E$28,5,FALSE).

That will generate random reals that add up to your values. If you need random integers, then you can round the results to the nearest integer, adding or subtracting from one or more of the random numbers to make sure they all add up to the chosen value.

Will that work for you?

thanks for your helping but how I do know the random generating for step 3?

[MrShorty]

I'm not sure I understand the latest question. The random numbers used in step 3 were generated in step 1. Step 2 takes the random numbers generated in step 1 and sums them up. In step 3, we then divide each random number by the sum of all of the random numbers (for that id) to get a normalized random number where the sum of those normalized random numbers will add up to 1. Then, by multiplying by the desired target sum, we will get a collection of random numbers that add up to the desired sum.

[fatoom91]

I'm not sure I understand the latest question. The random numbers used in step 3 were generated in step 1. Step 2 takes the random numbers generated in step 1 and sums them up. In step 3, we then divide each random number by the sum of all of the random numbers (for that id) to get a normalized random number where the sum of those normalized random numbers will add up to 1. Then, by multiplying by the desired target sum, we will get a collection of random numbers that add up to the desired sum.

can I do this steps without normalized ? because when I changed the orders from decimal to integer the sum of them will change too!
I mean I want to divide the sum for each User in the same ID in integer not in decimial bcz if I round them or change them to integer the sum of them will change too !

[Akuini]

Hi, fatoom91
Try this:

Please Login or Register  to view this content.

[fatoom91]

Hi, fatoom91
Try this:

Please Login or Register  to view this content.

thanks so much !!

[Akuini]

You're welcome, glad to help, & thanks for the feedback.