Randomize three variables subject to sum always equal to 1

[emsfeld]

Hi guys

I need a hand on this one, cuz I have absolutely no clue:

I need a code that randomly selects three real numbers between 0 and
and assigns these to three variables. I defined the variables as w(0)
w(1), w(2). The problem is that the sum has to equal 1! Always! S
that:

w(0) + w(1) + w(2) = 1

Help is very much appreciated!!!

Thanks

Emsfel

 

[emsfeld]

Hi,

yes..I know that too. But I am quite new to VBA and have no real
overview of its functions yet. I can pretty much write very basic
applications. My problem is that I dont even know how to make Excel
find a random number through VBA. Not even to mention about making the
three random variables between 0 and 1 subject to the condition that
the sum of them has to be 1 any time three random numbers are
selected!

Thx anyways

Regards

 

[mangesh_yadav]

try this link
http://www.ozgrid.com/VBA/RandomNumbers.htm

This UDF will generate x unique random numbers between any 2 number
you specify. Many thanks to J.E. McGimpsey for modifying this to wor
on more than 10 numbers.

The Code


Function RandLotto(Bottom As Integer, Top As Integer, _
Amount As Integer) As String
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i
For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Bottom To Bottom + Amount - 1
RandLotto = RandLotto & " " & iArr(i)
Next i
RandLotto = Trim(RandLotto)
End Functio

 

[emsfeld]

Thx for that...may be able to apply that to my problem

regard

 

[emsfeld]

Well...its good...but I need the numbers to sum up t
1.....always....and my understanding of VBA is not good enough t
manage that!

Help appreciated!!!

Regard

 

[mangesh_yadav]

Did you get the random numbers?
If yes, then you just need to find 2 random numbers and the third on
is equal to 1 - sum of first 2 random numbers

- Manges

 

[mangesh_yadav]

give me a moment, I'll modify it and give it to you

- manges

 

[emsfeld]

i see what u mean...but i would like to have positive numbers only

 

[JE McGimpsey]

One way:

Public Function ThreeRandsAddToOne() As Variant
Dim vTemp As Variant
Application.Volatile
ReDim vTemp(0 To 2)
vTemp(0) = Round(Rnd, 15)
vTemp(1) = Round(Rnd * (1 - vTemp(0)), 15)
vTemp(2) = 1 - (vTemp(0) + vTemp(1))
ThreeRandsAddToOne = Application.Transpose(vTemp)
End Function


Call by array-entering into a vertical range of three cells:

=ThreeRandsAddToOne()

 

[mangesh_yadav]

so you have got a solution now. sorry didn't have the time.

- Manges

 

[sulprobil]

Please keep in mind that your solution is NOT complete
random: The first number is (on average) higher than the
other two.

Exchange the indices of your array randomly, I suggest.

Kind regards,
sulprobil

 

[Tom Ogilvy]

The rnd function in VBA generates random numbers between 0 and 1. Since
you want an exact sum, you can round the numbers - how many decimals do you
want or does it not make a difference. This does two decimals. Lightly
tested.

Sub tester2()
Dim w(0 To 2) As Single
Randomize
For i = 0 To 1
Do
w(i) = Application.Round(Rnd(), 2)
Loop While w(0) + w(1) > 0.99
Sum = Sum + w(i)
msg = msg & w(i) & ", "
Next
w(2) = Application.Round(1 - Sum, 2)
Sum = Sum + w(2)
msg = msg & w(2) & " = " & Sum
MsgBox msg

End Sub

 

[JE McGimpsey]

If order is important, that certainly should be done.

For that matter, note that the third number isn't "completely random" at
any time - it's entirely determined by the first two.

 

[JE McGimpsey]

One way:

Public Function ThreeRandsAddToOne() As Variant
Dim vTemp As Variant
Dim dTemp As Double
Dim nRnd As Long
Dim i As Long
Application.Volatile
ReDim vTemp(0 To 2)
vTemp(0) = Round(Rnd, 15)
vTemp(1) = Round(Rnd * (1 - vTemp(0)), 15)
vTemp(2) = 1 - (vTemp(0) + vTemp(1))
For i = UBound(vTemp) To 1 Step -1
nRnd = Int(Rnd * i) + 1
dTemp = vTemp(nRnd)
vTemp(nRnd) = vTemp(i)
vTemp(i) = dTemp
Next i
ThreeRandsAddToOne = Application.Transpose(vTemp)
End Function

 

[sulprobil]

Sorry, no: Both of your programs lead to an average value
of 0.5 for the first variable, I think.

I suggest:
Public Function TRAT() As Variant
Dim vTemp As Variant
Dim i1, i2, i3 As Integer

Application.Volatile
ReDim vTemp(0 To 2)

i1 = Int(Rnd * 3)
i2 = (i1 + 1) Mod 3
i3 = (i1 + 2) Mod 3
vTemp(i1) = Round(Rnd, 15)
vTemp(i2) = Round(Rnd * (1 - vTemp(i1)), 15)
vTemp(i3) = 1 - vTemp(i1) - vTemp(i2)
TRAT = Application.Transpose(vTemp)
End Function

Kind regards,
sulprobil

 

[Tom Ogilvy]

how about something like:

Public Function ThreeRandsAddToOne() As Variant
Dim vTemp As Variant
Dim sum as Double
Application.Volatile
ReDim vTemp(0 To 2)
vTemp(0) = Round(Rnd, 15)
vTemp(1) = Round(Rnd, 15)
vTemp(2) = Round(Rnd, 15)
For i = 0 To 2
Sum = Sum + vTemp(i)
Next
vTemp(0) = Round(vTemp(0) / Sum, 15)
vTemp(1) = Round(vTemp(1) / Sum, 15)
vTemp(2) = Round(1 - (vTemp(0) + vTemp(1)), 15)
ThreeRandsAddToOne = Application.Transpose(vTemp)
End Function

 

[JE McGimpsey]

Yup. Misplaced parens:

Public Function ThreeRandsAddToOne() As Variant
Dim vTemp As Variant
Dim dTemp As Double
Dim nRnd As Long
Dim i As Long
Application.Volatile
ReDim vTemp(0 To 2)
vTemp(0) = Round(Rnd, 15)
vTemp(1) = Round(Rnd * (1 - vTemp(0)), 15)
vTemp(2) = 1 - (vTemp(0) + vTemp(1))
For i = UBound(vTemp) To 1 Step -1
nRnd = Int(Rnd * (i + 1))
dTemp = vTemp(nRnd)
vTemp(nRnd) = vTemp(i)
vTemp(i) = dTemp
Next i
ThreeRandsAddToOne = Application.Transpose(vTemp)
End Function

 

[sulprobil]

Hmm, I think your solution would generate too many nearly
equal values (0.333, 0.333, 0.334 for example) and less
extreme values such as (0.95, 0.04, 0.01). But here we
have to ask the original poster, what he intends to
simulate, do you agree?

Was fun.
sulprobil

 

[JE McGimpsey]

No. Why would it generate nearly equal values? The distribution will be
uniform to the extent that RND's distribution is...

 

[Tom Ogilvy]

I suspect that would be the mode, because if one number is 1/3, then all
three would be 1/3. However, your presumption that that is incorrect is
flawed. If you expect to generate 3 random numbers that sum to 1 and the
distribution of all such numbers should be uniformly distributed (which
appears to be your expectation); that seems to where the faulty logic lies.
Once two numbers are generated, the third is not random. Similarly, once
the first in generated, the 2nd and 3rd are constrained. If I generated an
extreme number then I would have a high probability of generating two less
extreme numbers to go with it. The distribution of each element of the
array appears to be the same - normal like with a mode around 1/3.

Put up your solution and let's have a look at it.