Jan 16 2021 08:12 PM - edited Jan 16 2021 08:16 PM
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
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
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.