• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

Random Number Generator to Equal a Sum

Hello Experts!

I am trying to create a VB code in 2010 that will take a sum (say 40), and randomly generate a set of random numbers (say 6 total numbers) that will equal that sum.  

For example, if I ran it one time, it would create:
8
7
5
9
6
5

and when I run it again, it would create:
7
10
4
8
3
8

I thank you all in advance!
0
mmylius
Asked:
mmylius
Advertise Here
1 Solution
 
nutschCommented:
Hi, that macro should do it for you, everytime in a new sheet.

Sub GetRandom()
Dim sht As Worksheet, dblSum As Double, cl As range
application.screenupdating=false

Const lgTarget As Long = 40
Const lgNumbers As Long = 6

Set sht = ActiveWorkbook.Sheets.Add

With range("A1:A" & lgNumbers)
    .FormulaR1C1 = "=rand()"
    .Value = .Value

    dblSum = application.WorksheetFunction.Sum(range("A1:A" & lgNumbers))

    For Each cl In .Cells
        cl.Value = Round((cl.Value * lgTarget / dblSum), 0)
    Next
    
    .Cells(lgNumbers, 1).Value = lgTarget - application.WorksheetFunction.Sum(range("A1:A" & lgNumbers - 1))
End With

application.screenupdating=true

End Sub

Open in new window

0
 
RobSampsonCommented:
Hi, that's a tough one!

This is VBScript, but you might be able to translate it to .NET.

Regards,

Rob.
intNumberToSumTo = 40
intNumbersRequired = 6

intLowerLimit = 1
intUpperLimit = intNumberToSumTo
strNumbers = ""
Randomize
For intNum = 1 To intNumbersRequired
	intUpperLimit = intUpperLimit - (intNumbersRequired + 1 - intNum)
	intNumber = Int((intUpperLimit - intLowerLimit) * Rnd + intLowerLimit)
	intUpperLimit = (intUpperLimit + (intNumbersRequired + 1 - intNum)) - intNumber
	If strNumbers = "" Then
		strNumbers = intNumber
	Else
		strNumbers = strNumbers & "," & intNumber
	End If
Next

MsgBox strNumbers

Open in new window

0
 
RobSampsonCommented:
nutsch....nice one!
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Start This Course
 
Bill PrewCommented:
Give this test a try.

Sub Test()
    MsgBox DoRand(30)
End Sub


Function DoRand(intNumber)
    Randomize
    intRemain = intNumber
    strList = ""
    While intRemain > 0
        intNext = Int((intRemain * Rnd) + 1)
        intRemain = intRemain - intNext
        If strList = "" Then
            strList = intNext
        Else
            strList = strList & "," & intNext
        End If
    Wend
    DoRand = strList
 End Function

Open in new window

~bp
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
That is a pretty cool answer by nutsch!

Here's another approach:
Option Explicit

Private Sub CommandButton1_Click()
    Dim randomSet As Variant
    randomSet = RandomSumSet(40, 6)
    
    ' ...do something with the set...
    ListBox1.Clear
    Dim i As Integer
    For i = LBound(randomSet) To UBound(randomSet)
        ListBox1.AddItem randomSet(i)
    Next
End Sub

Private Function RandomSumSet(ByVal sum As Integer, ByVal numDigits As Integer) As Variant
    Static Seeded As Boolean
    If Not Seeded Then
        Seeded = True
        Randomize
    End If
    
    Dim numbers() As Integer
    ReDim numbers(numDigits - 1)
    
    Dim startValue As Integer
    startValue = sum \ numDigits ' Integer Division
    
    Dim i As Integer
    For i = 1 To numDigits
        numbers(i - 1) = startValue
    Next
    If numDigits * startValue <> sum Then
        numbers(numDigits - 1) = sum - ((numDigits - 1) * startValue)
    End If
    
    Dim index As Integer
    Dim j As Integer
    For j = 1 To 100
        For i = 1 To numDigits
            Do
                index = Int((numDigits - 1 + 1) * Rnd + 1)
            Loop While index = i
            If Rnd >= 0.5 Then
                If numbers(i - 1) > 1 Then
                    numbers(i - 1) = numbers(i - 1) - 1
                    numbers(index - 1) = numbers(index - 1) + 1
                End If
            Else
                If numbers(index - 1) > 1 Then
                    numbers(index - 1) = numbers(index - 1) - 1
                    numbers(i - 1) = numbers(i - 1) + 1
                End If
            End If
        Next
    Next j
    
    RandomSumSet = numbers
End Function

Open in new window

0
 
patrickabCommented:
Cells A1:A6 contain =Randbetween(1,10) and A7 contains Sum(A1:A6). Code below and in the attached file.

Patrick
Sub rnandomnos()
While [A7] <> 40
Calculate
Wend
End Sub

To get a new set of numbers press F9 and then press the button

code below is in the attached file.

Patrick

Open in new window

randNos.xls
0
 
mmyliusAuthor Commented:
nutsch, thank you very much, that was exactly what I was looking for!
0
 
mmyliusAuthor Commented:
...going to fast.. thank you to everyone who answered also!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Enroll Today
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now