array of 12 random numbers that total 100

Hi Guys,

I am hoping someone can help me out really,

I am looking to generate an array of 12 random numbers that total 100.

The main point is, I want to specify a range that each of the 12 values must fall between.

Is this possible in Excel?

I have done very little VBA programming, but can probably struggle through.

Thanks in advance....

Try the attachment.

Maybe like this:

Please Login or Register  to view this content.

Formulas:

B1 is the target number

A3:A14 =RAND()

B3 and copy down to B14 =IF(A3=0, 0, ROUND( ($B$1 - SUM(B$2:B2) ) * A3 / ($A$15 - SUM(A$2:A2)), 0) )

A15 and copied to B15: =SUM(A3:A14)

B15 verifies that the numbers sum to the target.