How to Randomize data between a given range given a condition.

[chelule]

I have a problem randomizing data given a condition..
this is how my data looks like
Name quantity sale price sub total
chelule 4 10 40
kiplangat 4 50 200
ken 9 40 360
nancy 7 35 245
attitude 6 35 210
Jedidah 1 70 70
liz 8 45 360
rose 7 15 105

Total 10000

i want to be able to randomize quantity so that the the subtotals add up to a given total.

Kindly help

[NickyC]

you could either have random numbers in 7 of your 8 cateogries, with the 8th a calculated residual, or you could use a macro to generate a series of random numbers until the sum is the total you want. for example, if your 8 names are in cells A1:A8 and in cells B1:B1 you type and copy this formula:

=RANDBETWEEN(75,150)

then run this macro, which will generate random numbers until the sum of b1:b8 is 1000

Please Login or Register  to view this content.

[chelule]

Thanks thanks thanks!!!! it works though The macro keeps hanging my machine,one more thing, how can i use the same over many sheets considering the totals are supposed to be fetched from a different workbook.. how can i achieve that also.. i mean getting a value for a cell from another workbook

[NickyC]

Hi
I'm not sure i understand - do you want to import the totals from another workbook and randomise the numbers to add to that total? If so, you could for example copy or link the target value to a particular cell (say A1) and change the formula to e.g.

Please Login or Register  to view this content.

Be careful though - if you're using this macro many times over many sheets it will take a long time to run and could freeze your machine.

Also note that the formula doesn't give completely random numbers - it generates random numbers between 75 and 150. If it could generate any random number then it would take too long and freeze your computer.

Random numbers between 75 and 150 work well for 8 people and a total of 1000, but for other total or other numbers of people you will need to adjust the formula or it may not work. The more people you have and the wider the range of possible numbers, the more time the macro will take and the more likely your computer will freeze. You should also have the expected average number per person in the middle of your random number range - for example, with 4 people aiming for a total of 24 you would want and range with 6 as the mid-point (e.g. 4 to 8)