Leium asked in Computers & InternetSoftware · 6 months ago

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.

Update:

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

Relevance
  • DEBS
    Lv 7
    6 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.

  • 6 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
Still have questions? Get answers by asking now.