Simple Excel function that splits a numbered entered into one cell randomy and evenly over 3 others.

Question

Hi all,

I am looking for a simple function that will take a number entered into a single cell say 20 and divide it evenly and randomly over three other cells, none of the values can be 0.

ie. A1 = 20 then B1=6 C1=8 D1=6

Thanks!!

Answer 1

I don't have Excel in front of me, but something like this

B1: =Round(Rand()*(a1-3)+1, 0)
C1: =Round(Rand()*(a1-b1-2)+1, 0)
D1: =A1-B1-C1

• B1 is set to a number from 1 to A1-2
• C1 is set to a number from 1 to A1-B1-1
• D1 is set to what's left.

Answer 2

I think you would have to write a macro to expand the values into B1, C1, D1 automatically, but the way I would do it would be to put the following code into B1:

=RANDBETWEEN(1, (A1-2))

The following into C1:

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

The following into D1:

=A1-B1-C1

If you don't have the RANDBETWEEN() Function available here is how to enable it:

1. From the 'Tools' menu, select 'Add-Ins'
2. Find the reference to 'Analysis ToolPak' and put a tick in the box
3. Select 'OK

Answer 3

Without a macro, I dont see any way to get around having some temporary values shown.

Look at my illustration here which does what you are trying to achieve: http://www.gratisupload.dk/download/43627/

A2 holds the initial value to split

Temporary values:
C2,D2,E2 are just =RAND()

Your evenly, but radomly split values will apear in these cells:
C5 = A2 / (C2 + D2 + E2) * C2
D5 = A2 / (C2 + D2 + E2) * D2
E5 = A2 / (C2 + D2 + E2) * E2

Edit: Of course you could show the temporary values (C2, D2, E2) on a seperate sheet. Still, only to avoid the evil world of macros.