Random number with condition

[sandipgumtya]

Hi All,

        I am trying to random number for  24 hrs with below conditions:

1. No number should be more than 100.
2. The Sum of all the numbers should be 1440.

The requirement is the list should be fully random. In the "method tried" i have tried to do the same but can't achieve the first condition. Attaching the file. Need your help.

Thanks

Sandip Gumtya

[#2]

@sandipgumtya 

The first observation is that a uniform distribution over 0-100 will give an expected value of 1200 and not 1440, so larger values must be more likely than smaller ones.  In the attached, I have increased random numbers in the region of 0.5, leaving the limits at 0.0 and 1.0.  The extent to which the distribution is biased is determined once the specific set random values is known. 

Note: The form of the bias was chosen on the basis of computational convenience rather than rigour.

[#3]

@sandipgumtya  I like your method so I just tweaked it a little:

=B3-D3/SUM(D:D)*(SUM(B:B)-2*$B$27)

instead of multiplying the random number fraction by the total I multiplied it by the difference between the max sum and required total and then subtracted that from the individual max.  When the individual max are all the same it certainly equally random.  But if the individual max are not the same then you would need to have it proportional to the range of that individual compared to the total.  Is that something that you need?

[#4]

@Peter Bartholomew 

HI, Thanks. This works. If the data is required to be populated for 365 days of the year with the same daily limits and conditions, what tweak is required?

[#5]

@Peter Bartholomew 

Also in a couple of cases, the series violates the max(100) permissible value.

[#6]

@mtarler 

Thanks.

If the data is required to be populated for 365 days of the year in a single column with the same daily limits and conditions, what tweak is required? Is there a simpler approach?

[#7 · Accepted solution]

@sandipgumtya365  first off the more I thought about this problem the more I don't know if there is a 'perfect' solution.  that said my last answer was technically correct as it has a max value of 100 but wrong because I assume you don't want any negative numbers either and in some cases a negative value can result.  so that said I have a new solution that I do believe (and based on testing) will  always be between 0 and 100 and total 1440.  

as for doing the whole year I wasn't sure how you wanted or have that set up.  In the attached example I have the hours just continue to count up and then another column that calculates the day, but other options could work.

As for an explanation of what the solution is doing, I realized that if the sum of the Rnd numbers * 100 is more than the 1440 then scaling them by 1440/sum would be multiplying by less than 100 and hence always be acceptable.  So only if the sum of the Rnd numbers is <14.40 is it a potential problem.  so I figured that a sum of 1440 is an average of 0.6  so I figured if the sum < 14.40 (i.e. avg less than 0.6) then if I take 1-rnd/2 then the avg will be >0.6 and the problem will go away.

The problem with that solution is that it does 1/2 the 'range' of the numbers, but you do have to give something up with additional constraints.  That said I created a slightly better version in column G where I check if >=14.4 then use the normal fraction, if <=9.6 then use 1-rnd (without the divisor), and if between them use a divisor of 9.6/sum to maximize the range.   This last part is important as most of the time the average appears between those numbers and hence you want to maximize that range.  That said, I bet if you think about it some more you could tweak a little more out of it, but this is pretty good.

note this worksheet is starting to slowdown due to calculations but right now the sheet has 4 columns of calculations (rnd#, 1st try, 2nd try and 3rd try) but you can and should delete 2 of those columns.

Best of luck.

[#8]

@mtarler 

Thanks a ton. This seems to solve the issue. I will try some other iterations with the same idea. 

Thanks