RANDTOTAL function

[bambino_32]

hi all
I would like to create multi cell array formula which will give random numbers totaling specified number. I have started, but I really don't know how to tell function that it's been entered on 6 or 10 cells.

Please Login or Register  to view this content.

[Andrew-R]

Well there's your problem, you haven't closed your brackets properly. Try ...

Please Login or Register  to view this content.

Seriously I don't think this can be done in the form of a worksheet function - it would generate all kinds of logic problems.

It would be easy (well, easier) to write a Sub that distributed random numbers totalling a given value across a selected range of cells, but there are big problems with the mathematics as well. Say, for example, you wanted to generate random numbers from 1-10 totalling 20. If the first number generated is 10 and the second one 9 then your third number isn't going to be very random.

You could solve this problem by looping through, regenerating random numbers until the total happens to be the target ... so in the example above we've got a 10, a 9 and then we generate a 4, total 23 (bust!), so we regenerate the fist random number and get an 8 (total 21), then regenerate the second random number and get a 7 (total=19) and then regenerate the 3rd and get a 5 - BINGO!

Of course that could, theoretically, loop forever and it only works if you tell it how many random numbers you want in your total.

In short, it's dead hard.

[shg]

Try this:

Please Login or Register  to view this content.

Then for example, select A1:A10, and enter this formula:

=TRANSPOSE(RandTot(10000, 500, 1500))

The formula MUST be confirmed with Ctrl+Shift+Enter.

The Min and Max values have to reasonably center around the average value (here, 1000).

[bambino_32]

Thanks shg. i'll try this later...