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

Welcome to the forum.

In B1, =RANDBETWEEN(1, A1-2)

C1: =RANDBETWEEN(1, A1-B1-1)

D1: =A1-SUM(B1:C1)

Something like this?

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

Thinking about it, this has better statistics:

B1: =INT(RAND()^2 * (A1-2) + 1)

C1: =INT(RAND() * (A1-B1-1) + 1)

D1: =A1-B1-C1

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

Perfect spectrum

B1: =MATCH(513*RAND(), {0,54,105,153,198,240,279,315,348,378,405,429,450,468,483,495,504,510})

C1: =INT(RAND() * (A1-B1-1) + 1)

D1: D1: =A1-B1-C1

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