Random sequence that sums to predetermined number?

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi, I'm looking for a formula that uses random numbers that will sum to a predetermined number. For example, if the predetermined number is 15, one iteration might look like: 2+3+1+5+5 = 15, second iteration: 5+1+6+2+1=15, etc.

That's the basic idea, but if additionally it could be constrained to always use a certain number of variables as well, that would be great. The two examples above use 5 numbers that always will equal the predetermined number.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Do you also have a range? I mean like "sum of numbers between..." or any number?
 
Upvote 0
Neath :)
VBA Code:
Sub test()
  Dim myTarget As Long
  Dim numberOfVariables As Long
  Dim randLowerLimit As Long
  Dim randUpperLimit As Long
 
  myTarget = 15
  numberOfVariables = 5
  randLowerLimit = 1
  randUpperLimit = 9
 
  MsgBox randomSum(myTarget, numberOfVariables, randLowerLimit, randUpperLimit)
End Sub

Function randomSum(ByRef target As Long, ByRef numVar As Long, ByRef min As Long, ByRef max As Long) As String
  Dim result As Long, numbers() As Long
  Dim myFormula As String

  ReDim numbers(numVar)
  If numVar * max  < target Then
    randomSum  = "Can not reach to target."
  Else
    Do While target <> result
      myFormula = ""
      For i = 0 To numVar - 1
        numbers(i) = WorksheetFunction.RandBetween(min, max)
        myFormula = myFormula & numbers(i) & "+"
      Next
      result = Application.WorksheetFunction.Sum(numbers)
    Loop
    randomSum = Left(myFormula, Len(myFormula) - 1) & " = " & result
  End If
End Function
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top