Forum Discussion
Random number with condition
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.
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?
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?
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.
Thanks a ton. This seems to solve the issue. I will try some other iterations with the same idea.
Thanks
