Can this be done in Excel

Occasional Visitor





Can Excel produce 5 random numbers that equal one number that I input

Record Book Example.jpg


2 Replies



Here is a custom VBA function:

Function RandomToSum(n As Long, total As Long)
    ReDim rv(1 To n, 1 To 1) As Long
    ReDim r(1 To n) As Double
    Dim i As Long
    Dim s As Double
    Dim t As Long
    Dim d As Double
    Dim j As Long
    For i = 1 To n
        r(i) = Rnd
        s = s + r(i)
    Next i
    For i = 1 To n
        rv(i, 1) = Int(total * r(i) / s)
        t = t + rv(i, 1)
    Next i
    d = total - t
    For i = 1 To d
        j = Application.RandBetween(1, n)
        rv(j, 1) = rv(j, 1) + 1
    Next i
    RandomToSum = rv
End Function

Select B5:B9.

Enter the following formula and confirm it with Ctrl+Shift+Enter to turn it into an array formula:




Fill to the right to F5:F9



Another possibility, if you turn on iteration (Options\Formulas, check iterations box, I set the max number of iterations to 1000) is using a circular reference. If it stops at the max number of iterations without finding a solution, you should be able to hit the F9 key until it does (or increase the max iterations). Note - it appears iteration may need to be re-activated whenever the workbook is closed/reopened.


However, note that if you force the cell to recompute (for example, clicking in the formula bar and hitting enter), then the formulas will recalculate and give you a different set. So, you may want to copy/paste special value the set of numbers (in place or to another location) after you've computed them if this could be an issue.