Excel - Random generate, possible, question, example, without

edgeon

Somewhat of a strange (if not impossible?) Excel question:

I have ten cells, that must equal 1 (100%). How can I generate random percentages in each of those cells that are greater than zero, less than 100%, and populate every cell?

For example, a random pick may be { 3%, 12%, 22%, 10%....and so forth }

Is this possible without VBA?

Kapie

ohai Wrote:
-------------------------------------------------------
> Yeah. There are lots of ways to do this. The
> simplest way I can think of is:
>
> 1) In column A, use =rand() for each of the 10
> cells. This generates a uniform random variable (0
> to 1) for each cell.
> 2) Then, take the sum of all the column A values.
> 3) In column B, divide the column A values by the
> sum from 2). The column B values will be random
> and will sum to 1.

bingo

HuskyGrad2010

Haha. Yeah....let me guess you are modeling different portfolios.




On one sheet create a sheet of randomly generated nos.
A B C D
1 rand rand rand rand
2 rand rand rand rand

Copy those numbers into another sheet, and paste special the values.

A B C D
1 X S K H
2 G F O Y

On the right most column, sum the prvious nos.

A B C D E
1 X S K H SUM(A1:E1)
2 G F O Y SUM(A2:E2)



In another spreadsheet, divide each cell by the total on the right to get the percentage....


that probably made no sense

yalo

ohai Wrote:
-------------------------------------------------------
> Yeah. There are lots of ways to do this. The
> simplest way I can think of is:
>
> 1) In column A, use =rand() for each of the 10
> cells. This generates a uniform random variable (0
> to 1) for each cell.
> 2) Then, take the sum of all the column A values.
> 3) In column B, divide the column A values by the
> sum from 2). The column B values will be random
> and will sum to 1.


+1

SFoyil

ohai Wrote:
-------------------------------------------------------
> Yeah. There are lots of ways to do this. The
> simplest way I can think of is:
>
> 1) In column A, use =rand() for each of the 10
> cells. This generates a uniform random variable (0
> to 1) for each cell.
> 2) Then, take the sum of all the column A values.
> 3) In column B, divide the column A values by the
> sum from 2). The column B values will be random
> and will sum to 1.

While this would be a random allocation it would not be uniformly distributed. For random allocations, it would be more practical looking at algorithms of generating random points on a simplex (since one might want to consider upper constrait for each component below 1). I started a discussion about that on wilmott a few years ago, it might be easy to find suggested solutions there.

stockjaguar

if u r so worried abt uniform allocation, try this
A1:A10= 1:10
b1 ==RANDBETWEEN(0,20)
b2 t0 b9 =RANDBETWEEN(0,A2*(100-SUM($B$1:B1)))/10
b10 = =100-SUM(B1:B9)

Carson

Yeah, in ohai's way, it won't be really random. Better to generate a random value for each cell, and divide each cell by the total value for that set. It'll take a couple of spreadsheets to do that tho.

dreampak

maratikus Wrote:
-------------------------------------------------------
> ohai Wrote:
> --------------------------------------------------
> -----
> > Yeah. There are lots of ways to do this. The
> > simplest way I can think of is:
> >
> > 1) In column A, use =rand() for each of the 10
> > cells. This generates a uniform random variable
> (0
> > to 1) for each cell.
> > 2) Then, take the sum of all the column A
> values.
> > 3) In column B, divide the column A values by
> the
> > sum from 2). The column B values will be random
> > and will sum to 1.
>
> While this would be a random allocation it would
> not be uniformly distributed. For random
> allocations, it would be more practical looking at
> algorithms of generating random points on a
> simplex (since one might want to consider upper
> constrait for each component below 1). I started
> a discussion about that on wilmott a few years
> ago, it might be easy to find suggested solutions
> there.

Well if you ignore how you derived the 10 numbers (which add up to 1) then they sound pretty random to me...

soverby

ZeroBonus Wrote:
-------------------------------------------------------
> Well if you ignore how you derived the 10 numbers
> (which add up to 1) then they sound pretty random
> to me...

Think about simple example of two variables. You simulate a random point on a square first and then you project that point on the main diagonal. Clearly, there will be significantly more 'weight' in the middle than on the tails. That would be appropriate in certain situations but not appropriate in some other ones, for example, if your want to generate a random allocation that is uniformly distributed on the space of feasible allocations.

Windjam

maratikus Wrote:
-------------------------------------------------------
> ZeroBonus Wrote:
> --------------------------------------------------
> -----
> > Well if you ignore how you derived the 10
> numbers
> > (which add up to 1) then they sound pretty
> random
> > to me...
>
> Think about simple example of two variables. You
> simulate a random point on a square first and then
> you project that point on the main diagonal.
> Clearly, there will be significantly more 'weight'
> in the middle than on the tails. That would be
> appropriate in certain situations but not
> appropriate in some other ones, for example, if
> your want to generate a random allocation that is
> uniformly distributed on the space of feasible
> allocations.

I don't understand what you said but if I need 10 numbers that add up to 1 and I can get those 10 numbers (a different combo each time) which are 'derived' from another sample of 10 numbers, I don't see how that isn't purely random

If you 'hide' column A in ohai's solution, you will see 10 random numbers that add up to 1 every time.