Random Branching

I have 2 cells where I have random numbers that total 100. My problem is that I want to branch each of these cells into 3 more random cells that total the previous cell.

For example

A1 = 75: B1=30, C1=22, D1=23


A2 = 25: B2=11, C2=9, D2=5


I also don't want any of the 3 cells to be higher than the number 50. I would appreciate any help given, thanks in advance.

Please see the attached.

I've used a user defined array function. You can see the code in Module1 when you hit Alt F11.

Originally Posted by mrice

Please see the attached.
I've used a user defined array function. You can see the code in Module1 when you hit Alt F11.

With your code, any in 3 cells in B, C, D may higher 50.
I suggest:

PHP Code: 

Function ThreeRandomNumbers(MySum) As Variant
StartAgain:
X = WorksheetFunction.RandBetween(1, 50)
Y = WorksheetFunction.RandBetween(1, 50)
Z = MySum - X - Y
If Z < 1 Then GoTo StartAgain
If Z > 50 Then GoTo StartAgain
ThreeRandomNumbers = Array(X, Y, Z)
End Function 


Also A1 and A2 are random too:
A1=RANDBETWEEN(1,100)
B1=100-A1

Originally Posted by 500bloc

I thank you for this, but for the strangest reason my computer has problems with Macro. Is their another way this can be done?

As my knowdledge, formula can't do. If A1 random 99, B1 random 1, C1 random 2, D1 must be higher than 50 (=99-1-2=96?). With code, excel can start again automatically till getting right result; but with formula, you must press F9 till getting right result.

this should do it i think

not if you want random.so say 75 is split randomly first number is 5 second is 10 the last must =60 you would have to use code even using max() would not fix it as youd get a disproportionate returns of 50