# using excel is there a formula, where i can a set value in one cell, and it randomly divide that number between other cells.?

using excel

is there a formula, where i can a set value in one cell, and it randomly divide that number between other cells.

example. cell a1 has the value 10

it randomly divides it ober cells b1,2,3,4,5

now b1=2 b2=1 b3=4 b4=0 b5=3

then each time the page updates these values will then randomly redivded themselves again.

i need the value to be completely divided randomly, so that cells b1-5 total a1 with out a smaller or exceeding total value.

### 2 Answers

- DEBSLv 76 months ago
First thought was no, but then I started playing around. I haven't gotten it to work perfect, and part of that is that I'm not positive of your requirement.

The plan would be to divide 10 by a RandBetween formula. You'd need to take into account the fact that each cell can only be big enough to allow at least a 1 in the remaining cells. So your first random cell is actually =RandBetween(1,10-4). You'd have to reduce the upper limit of the random statement based on the value returned so far to allow at least 1 in the remaining cells.

The other part I'm not convinced keeps 100% randomness is the fact that by always starting with the first cell and then reducing the values available each time your later cells have a smaller chance of being large. (They could only be large if the first 4 cells randomly were 1, for example.)

To get around this, you could have 5 rows where the formulas are adjusted so that each cell gets a chance of being first, second, third, etc. You can hide these rows and have one row at the top with your real answer. That one would reference one of the rows randomly using a vlookup based on RandBetween(1,5) formula.

Confusing enough? Because I'm barely following it at this point, but I know it would be possible. Worth it? Who knows, but possible it is.

- garbo7441Lv 76 months ago
Edit: Here is a rather less than elegant solution using VBA. As duplicate entries must be allowed for any entry other than a multiple of 5, there may be multiple values of a number, even '0', in each generated set.

Copy this event handler to the clipboard (highlight the entire code, right click inside the code and 'Copy'):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, tVal

On Error GoTo Errhandler

If Target.Address(0, 0) = "A1" Then

Application.EnableEvents = False

Range("B1:B5").ClearContents

tgtVal = Target.Value

For i = 1 To 4

retry:

tVal = Application.RoundDown(Rnd() * tgtVal / 2, 0)

If tVal = tgtValue Then

GoTo retry

ElseIf Application.CountIf(Range("B1:B4"), tVal) = 0 Then

If Application.Sum(Range("B1:B4")) + tVal > tgtVal Then

GoTo retry

Else

Range("B" & i).Value = tVal

End If

End If

Next i

Range("B5").Value = tgtVal - Application.Sum(Range("B1:B4"))

For Each c In Range("B1:B4")

If c.Value = "" Then

c.Value = 0

End If

Next c

Errhandler:

Application.EnableEvents = True

End If

End Sub

Select the worksheet you wish to employ this in and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Save the workbook as an Excel Macro-Enabled Workbook to preserve the functionality in the future.

Enter any value in A1 in your worksheet an ENTER, TAB, or click another cell. A random set will be generated in B1:B5. Each entry in A1 will generate a new set.

- Log in to reply to the answers

Good point about the strict 5 cell output.