I'm trying to simulate a variety of allocations to an investment
portfolio; eg, 10% to stocks, 20% to corporate bonds, 70% to
government bonds, etc. I'd like to randomly generate portfolio
allocations, the problem being that they have to total 100%. Is there
a way with the RANDBETWEEN function to do something like this?
The following assumes that you do not want 0% for any asset class.
The formula (in A1, say) for the first of, say, 8 asset classes might
be:
=randbetween(1, 100 - 7)
The formula (in A2) for the second of, say, 8 asset classes is:
=randbetween(1, 100 - sum($A$1:A1) - (8-count($A$1:A1)-1)))
Copy that formula for all but the last asset class. "A1" will be
updated automatically.
The formula for the last (8th) asset class is:
=100 - sum(A1:A7)
Note that all numbers are integers, not percentages. If we divide
each formula by 100, then 100*SUM(...) might not be exactly the same
as the sum of the integral values (before dividing by 100) -- although
ROUND(100*SUM(...),0) might be(?). If you can tolerate that, fine.
Otherwise, put =A1/100 in another column, and copy down.
Finally, beware that RANDBETWEEN() will be recomputed every time you
change any cell in the worksheet. Usually, that is undesirable. You
can minimize the impact by disabling auto recalculation. I would
avoid it by creating the VBA function MyRandBetween().
