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? Thanks,

Andrew

Hi Andrew,

Divide each random number by the sum of all random numbers and use that to multiply with the total available investment.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

<(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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? Thanks,
|
| Andrew
|

Very clever -- thanks!

Andrew

Hello,

I would not necessarily divide by the sum. The resulting distribution
is biased.
There are several ways. Three possible approaches are shown here:
http://www.sulprobil.com/html/randsum1.html

Regards,
Bernd

Thanks very much -- very helpful.

Andrew

On Feb 6, 12:37 pm, "agbi...@hotmail.com" <agbi...@hotmail.com> wrote:
> 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().