# Can this be done in Excel

Occasional Visitor

# Can this be done in Excel

15=?+?+?+?+?

40=?+?+?+?+?

28=?+?+?+?+?

16=?+?+?+?+?

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

2 Replies

# Re: Can this be done in Excel

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
Randomize
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:

=RandomToSum(5,B10)

Fill to the right to F5:F9

# Re: Can this be done in Excel

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.