Random number generation within the range

[kosteg]

Hello everyone,

I need to generate 5 random numbers within the range from 5-80, but total sum must be always equal to 100.

Example:

5/5/5/5/80
10/20/30/20/20
and so on..


any ideas ?

 

[Ruddles]

A1: =INT(RAND()*75+5)
B1: =MAX(MIN(INT(RAND()*(80-A1)+5),85-A1),5)
C1: =MAX(MIN(INT(RAND()*(75-A1-B1)+5),80-A1-B1),5)
D1: =MAX(MIN(INT(RAND()*(70-A1-B1-C1)+5),75-A1-B1-C1),5)
E1: =100-A1-B1-C1-D1

Press F9 repeatedly.

Yes?

Note: there are a lot of 5's in there because you're allowing the early numbers to vary all the way up to 80, so the values of subsequent numbers are being constrained more and more towards 5. They're not really random numbers, of course, because of these constraints.

 

[kosteg]

Thank you for your suggestion but as you mentioned, we constraining the function. I had another approach but now I am strugle with limits on in. My proposal was:

A1=randbetween(5,23.75)
A2=randbetween(5,23.75)
A3=randbetween(5,23.75)
A4=randbetween(5,23.75)
A5=randbetween(5,23.75)
A6=sum(A1:A5)

B1=(A1/$A$6)*100
B2=(A2/$A$6)*100
B3=(A3/$A$6)*100
B4=(A4/$A$6)*100
B5=(A5/$A$6)*100

5 to 23.75 are lower limits which is not less than 5. Then question arise how to set up higher limit 1/1.25. Can I use "and" function ???

 

[Ruddles]

Did you try my formulae? Did they not produce the desired results?

 

[kosteg]

Yes I did, the probability of distribution is decreasing to 4th number. Number is equal to 5 in every 8/10 try.

 

[Ruddles]

Yes, I would expect that because your constraints are 'squeezing' the possible range of numbers as the set goes from the 1st term to the 4th. Obviously if the first term can be anything up to 80, there's a possibility that you'll end up with 80/5/5/5/5 approximately 1.33% of the time.