标题: Excel - Random [打印本页] 作者: edgeon 时间: 2011-10-14 08:26 标题: Excel - Random
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 时间: 2011-10-14 08:37
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 时间: 2011-10-14 08:42
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 时间: 2011-10-14 08:48
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 时间: 2011-10-14 08:53
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 时间: 2011-10-14 08:59
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 时间: 2011-10-14 09:04
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 时间: 2011-10-14 09:10
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 时间: 2011-10-14 09:15
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 时间: 2011-10-14 09:21
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.作者: CFA4Techie 时间: 2011-10-14 09:26
ZeroBonus Wrote:
> 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.
ZeroBonus, I was saying that whenever you generate a random variable it's good to have an idea of its distribution. I care very much about that and I was suggesting considering the impact of the suggested algorithm on the distribution of the results. I am sorry about the confusion. My comments would've been more appropriate for wilmott.com.
