Jan 16 2021 08:12 PM - edited Jan 16 2021 08:16 PM
15=?+?+?+?+?
40=?+?+?+?+?
28=?+?+?+?+?
16=?+?+?+?+?
Can Excel produce 5 random numbers that equal one number that I input
Jan 17 2021 05:46 AM
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
Jan 17 2021 11:23 PM - edited Jan 17 2021 11:24 PM
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.