Total Price has to be divided into 30 Random Nos within a range

[lavan_joy]

Hi experts,
I need your help. Attached the sample file and screenshot


Lower upper limit will be changed everytime manually-(B2 & C2)
Price will be changed everytime manually (B4)
Need 30 random numbers within lower & upper limit.
TOTAL of those random numbers should be equal to Price (B35 = B4)



Thanks a lot.

Regards
Lavan Joy

[Special-K]

I dont think youve explained this clearly enough, I'm sure you're going to add something to the description that wasn't there in the first place.
Nevertheless...

Isn't this simply 45500/30 ?
Or are you saying the figure 500 and 3000 must be present in that list?
If the latter then...

to ensure 500 is in the list
in F5
=B2

to ensure 3000 is in the list
in F6
=C2

then in F7:F34
=(45500-(B2+C2))/28

[Richard Buttrey]

Are the numbers to be randomised?

And in which case the upper limit will need to be restricted and be less than 3000 unless you accept there will be negative numbers.

[lavan_joy]

Thanks for your help.Sorry for my bad explanation.

Lower and upper limit will be changed everytime manually-(B2 & C2)
Price will be changed everytime manually (B4)
Need 30 random numbers within lower & upper limit.
TOTAL of those random numbers should be equal to Price (B35 = B4)

Edited the post.
Please check the new excel file.
Thanks

[Richard Buttrey]

I don't see how this is possible without some other rule or rules.

If you think about it if the numbers are truly random between those limits then the 'average' random number will be
(45500-30x500) / 30 +500 = 1516.67

However you're dealing with random numbers and there's no guarantee what the total will be.
As the problem is stated, in extremis it will be between 30*500 and 30*300, i.e. a total between 15000 & 90000

More information / limitations are required

[sourabhg98]

This problem is a lot more complex than it seems.

I am not 100% sure if the distribution of the result generated by this method is Uniform, but I have tried my best.

Please Login or Register  to view this content.

Put above code in a standard module.

Syntax of the Function
=gen_rand(number of random integers required, lower limit, upper limit, target sum)


And to use this function just select the range, i.e. B5:B34, then press F2, in the first cell of the selected range now type in the formula "=gen_rand(30,B2,C2,B4)" and then press Ctrl + Enter
Checked attached for the implemented solution.
Sample.xlsm

Hope this helps.

[Richard Buttrey]

Neat. Well done.

It does raise the rather philosophical question as to whether a number generator system that itself works to a set of rules could be described as a random system.
Discuss...

[avk]

You required min & max number from list or generation of number list.
For Min in "B2"

Please Login or Register  to view this content.

In "C2"

Please Login or Register  to view this content.

In "B4" : =B35

For Generation of number list between 500 to 3000
In "B5"

Please Login or Register  to view this content.

[lavan_joy]

Sourabh! You saved my day. Thanks a lot for the code. My sincere thanks to Richard Buttrey and avk for your help.