Random nos generation for a specific total

[shanmugaraj]

Hi
i want to generate sales amount randomly for an year
Data :
Max Value 500
Min Value 100
Total rows 12
Sum should be 1000

I have used D1 =ROUND(RAND()*($B$1-$B$2)+$B$2) and got datas from D1 to D12

I need the sum of D1 to D2 as 1000

Please help....
Thanks in Advance

 

[mikerickson]

If the min value is 100, the minimum sum is 12*100 = 1200 > 1000

 

[shanmugaraj]

Respected Sir,
when i try with random nos generated using excel, the total is not 1200..
the numbers are generated automaically around this values
i want total to be a value wich i specify in B4 column (1000 here)

 

[mikerickson]

I assumed that the OP has a typo and that you want Sum D1:D12 =1000.
If you choose 12 random numbers, each of which is between 100 and 500, their sum will be between 1200 and 6000.

If the OP is correct and you want D1+D2 = 1000,
D1 =500
D2 = 500
is the only way to meet your conditions.

 

[shanmugaraj]

Hi
i want to generate sales amount randomly for an year
Data Conditions :
===============
Max Value : 500
Min Value : 100
Total rows : 12
Sum of total sales of the year should be 2500

cell B1 = 500
Cell B2 = 100


I have used D1 =ROUND(RAND()*($B$1-$B$2)+$B$2) and got datas from D1 to D12

I need the sum of D1 to D12 as 2500


DATA
====
From D1 to D12, i got random no for all the cells using Cell = ROUND(RAND()*($B$1-$B$2)+$B$2)
D1=250
D2=494
D3=204
D4=404
D5=448
D6=295
D7=478
D8=358
D9=422
D10=350
D12=304
D11=327

SUM = 4334


Sum = 4334
but i need sum as 2500 within the range of the 100 to 500


Please help....
Thanks in Advance

Shanmugaraj
0+91+97911-33085
India

 

[DonkeyOte]

I think the issue is you're not really using true random numbers if your range of possible values can exceed the required result... 6000 is the largest possible number using your low and upper twelve times... you would have to adjust you upper and lower bands each time in your RND function based on the sum of the preceeding rows to ensure the total of all 12 never exceeded 2500

 

[shanmugaraj]

hi ,
i just want to know how to make sum of random no to a speciic value so that i can upload dummy data where sum = 2500... also the min should be 100, max as 500 for each row

like
152
355
142
102
114
112
447
105
120
264
453
134

= 2500

 

[mikerickson]

I can do it to get a sum of 3600.
Put =RAND() in A1:A12
In B1, put =((A3-AVERAGE(A1:A12))*(500+100)/2)+((500+100)/2)

and fill down.

Column B will have your figures.

With other target sums, the problem is that the average sought is not mid-way between the Min allowed value (100) and the Max allowed value (500)

 

[mikerickson]

Using Named values, a solution is possible.
Name:MinAllowed RefersTo: =100
Name:MaxAllowed RefersTo: = 500
Name:Goal RefersTo:=2000
Name:GoalAvg RefersTo = Goal/12

Again in A1:A12, = RAND()

In B1 = ((A1-Average($A$1:$A$12))*MIN(GoalAvg-MinAllowed, MaxAllowed-GoalAvg)) + GoalAvg
Filled down to B12, this will give your list of 12 values that sum to Goal.

However the further that Goal is from (MinAllowed+MaxAllowed)/12, the narrower the range of the results.

I just noticed: in the previous post the argument of AVERAGE should have absolute references.