Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Random Number Generator to Equal a Sum

Posted on 2011-03-08
8
Medium Priority
?
1,056 Views
Last Modified: 2012-05-11
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
Comment
Question by:mmylius
8 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 1000 total points
ID: 35076215
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 35076428
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 35076435
nutsch....nice one!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 59

Expert Comment

by:Bill Prew
ID: 35076448
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
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 35079872
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
 
LVL 45

Expert Comment

by:patrickab
ID: 35081878
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
 

Author Closing Comment

by:mmylius
ID: 35083605
nutsch, thank you very much, that was exactly what I was looking for!
0
 

Author Comment

by:mmylius
ID: 35083613
...going to fast.. thank you to everyone who answered also!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question