How to Split a number in one cell evenly over several?

Hi all,

Probably a simple little function, help would be appreciated!

I'd like to take a number entered into one cell ie. A1 = 20

and split it randomly but evenly over 3 other cells ie.
B1=6
C1=8
D1=6

Number should be whole and no zeros?

Thanks all!

Something like this?

Edit: Ahh, correct shg.. I skip "randomly" part

That works but it doesn't divide numbers evently (biggest numbers are in first column)...

I get this one more fairly (at least look for me on first sight)...

=RANDBETWEEN(1;ROUNDDOWN(($A2+1)/2;0))
=RANDBETWEEN(1;ROUNDDOWN(($A2-B2-1);0))
=A2-B2-C2

Or maybe this:

=RANDBETWEEN(SQRT($A2);ROUNDDOWN(($A2)/2;0))
=RANDBETWEEN(SQRT($A2);ROUNDDOWN(($A2)/2;0))
=A2-B2-C2

Hmmm... I'll need to look little on the numbers (to figure it out ) but, on first sight, I think this is not good either...

Let say we have 20...

Randomly and evenly I suppose is (all variations):

6+6+8
6+7+7
6+8+6
7+6+7
7+7+6
7+8+5
8+6+6
8+7+5
8+8+4

But it depends how low can you go (min is 6 , or 5, or 4 or 3 or less ??)

Above example is with min 6 and max 8 (not 4 as last example could trick you because 4 is there because of 20 - SUM(randbetween(6,8), randbetween(6,8))...

Now... obviously, last column has bigger range but also groupped around number 6 (first two has 33% for getting 6,7 or 8 and last column 11% for 8, 22% for 7, 33% for 6, 22% for 5 and 11% for 4 (where 4 and 5 can't appear in first two columns)...

now... I don't know can this be arranged better but your solution might give, correct me if I'm wrong:
0, 0, 20
or
1,2,17
but also
18, 1, 1 etc

when RAND is nearby 0 or 1...

Wich won't give evenly distribution...

Therefore I pick for 20 min and max range 4 (sqrt 20) and 10 (20/2)...

This also isn't very good (because it can be 10, 10, 0) but I think it's better than just RAND (i.e. 0,0,20)... But it depends on defining range so it can be tuned better (user should specify here what's his expectation)...

Impatient to hear your answer and ideas

To expand on the same question...

I need to do the same thing but I need to distribute my sum (ex 20) across 12 columns where each column has a different weight (ex .09), still returning integers.

Hey guys,

I am looking for similar solution (I think). I have posted my thread, but no luck yet, also in other forums. Here are the links to my thread.
Excelguru
And in this forum but this is old.

Any suggestions?