4 Random Numbers that equal the same Total

[girlfriday]

Could you tell me how to make a Excel spreadsheet where I could make 4 random numbers that when added together will equal the same total?

Such as the total is 21 so different 4 numbers would be 3558, 3648, 3747, 3828, etc

So I want to have a different total each time and the 4 digit numbers will be automatically generated for me.

Thank you ever so much!

 

[oldbrewer]

Girl Friday is strangely quiet... she sets a nice challenge but does not offer clarification....

 

[oldbrewer]

Could you tell me how to make a Excel spreadsheet where I could make 4 random numbers that when added together will equal the same total?

Such as the total is 21 so different 4 numbers would be 3558, 3648, 3747, 3828, etc

So I want to have a different total each time and the 4 digit numbers will be automatically generated for me.

Thank you ever so much!

she wants to type in 21 and get 4 random numbers that add to 21 - just 4 numbers
then she wants to type in 3,788 and get 4 random numbers that add to 3788

 

[Rick Rothstein]

she wants to type in 21 and get 4 random numbers that add to 21 - just 4 numbers
then she wants to type in 3,788 and get 4 random numbers that add to 3788

That cannot be right... the OP said "So I want to have a different total each time and the 4 digit numbers will be automatically generated for me." If the 3558, 3648, etc. are what the OP meant by 4-digit numbers for the total of 21, then the maximum "different total" would be 36, made of four 9s (which, of course, is not very random).

 

[oldbrewer]

Rick - I think she chooses a number, 21, 77, 3499 whatever
and she wants 4 random numbers that add up to that total
she gives 21 as example and 3,5,5,8 would be acceptable for 77 9, 27, 30, 11 would be ok

 

[Rick Rothstein]

Rick - I think she chooses a number, 21, 77, 3499 whatever
and she wants 4 random numbers that add up to that total
she gives 21 as example and 3,5,5,8 would be acceptable for 77 9, 27, 30, 11 would be ok

You could be right, but I really do not read it that way. In any event, your musing in Message #21 is right on... we need the OP to come back and clarify exactly what she is looking for.

 

[shg]

Mr shg has a similar problem with his solution. It returns numbers from 0 to 21 (with diminishing probability).

That's true -- I completely missed that she wanted a 4-digit number whose digits sum to the desired total.

 

[shg]

Sooo ...

	A​	B​
1​	sum​	num​
2​	1​	1000​
3​	2​	1010​
4​	3​	1011​
5​	4​	2011​
6​	5​	1130​
7​	6​	2040​
8​	7​	2410​
9​	8​	2402​
10​	9​	4122​
11​	10​	1081​
12​	11​	2306​
13​	12​	3315​
14​	13​	3811​
15​	14​	5414​
16​	15​	1905​
17​	16​	1375​

Function GF(iSum As Long, Optional bVolatile As Boolean = False) As Variant
    Static col      As Collection
    Dim jSum        As Long
    Dim i           As Long

    If bVolatile Then Application.Volatile
    
    If col Is Nothing Then
        Set col = New Collection
        For i = 1 To 36
            col.Add New Collection
        Next i

        For i = 1000 To 9999
            jSum = DigSum(StrConv(i, vbFromUnicode))
            col.Item(jSum).Add i
        Next i
    End If

    If iSum >= 1 And iSum <= 36 Then
        Randomize
        With col.Item(iSum)
            GF = .Item(Int(Rnd() * .Count) + 1)
        End With
    Else
        GF = CVErr(xlErrValue)
    End If
End Function

Function DigSum(ab() As Byte) As Long
    Dim i           As Long

    For i = 0 To UBound(ab)
        DigSum = DigSum + ab(i) - 48
    Next i
End Function

 

[Rick Rothstein]

Randomness is surprisingly delicate and random systems have to be treated very carefully and conservatively in order not to leave finger prints. Thats is my basic point. And anyway, I did a little test and my 1.36 million monkeys took 25 seconds to generate and document 100,000 acceptible solutions, an average of 250 micro seconds and with a 99.99% chance of doing it in under 22 milliseconds, so there is no perceptible delay with the monkey approach and therefore no reason not to use it.

Here is the code to do it

Sub Rand4()
'Select as many output rows as you want in a column and the results will be output to the right.
'For a single solution, just select one cell.
Const limit As Long = 21
Dim r(1 To 4) As Long, rSum As Long, i As Long, c As Range, attemptsCount As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each c In Selection
    attemptsCount = 0
    Do
        rSum = 0
        For i = 1 To 4
            r(i) = Round(Rnd() * 9 + 0.5, 0)
            rSum = rSum + r(i)
        Next i
        attemptsCount = attemptsCount + 1
    Loop Until rSum = limit
    c.Value2 = attemptsCount
    For i = 1 To 4
        c.Offset(0, i).Value2 = r(i)
    Next i
Next c
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Putting aside the fact that VB's random number generator is not all that good at producing sequences of numbers that are anywhere near "random", I think an argument can be made that the above code is flawed, random-wise, as well. What make a set of random numbers random is their sequence... as long as your proceed along the sequence, in order, you will be looking at random numbers, but if you skip around within the sequence, there is nothing assuring the numbers you skip to will be a random sequence (unpredictable, yes, random, maybe not). Your code grabs 4 numbers from the sequence of random numbers generated by VB and throws them away if they do not total to the limit being sought and grabs the next 4 numbers in the sequence to see of they total to the limit or not; hence, you are skipping through the random number sequence by fours and skipping over three sets of numbers that might, in fact, total to the limit but are not being examined. For example, let's say these are a set of random numbers generated by VB's Rnd function....

8759871671232829

Your code would first look at 8759, see that it does not total 21, then look at 8716 and see it does not equal 21, then look at 7123 and see it does not equal 21 and then finally arrive at 2829 which does equal 21 and report that as the result. However, your code skipped over the four random numbers from that sequence highlighted in red below...

8759871671232829

which also total 21 and come earlier in the sequence than your reported result. The following code would find 7167 and any other grouping of 4 digits from the random number sequence no matter where in the sequence they were located at. Note that I made the code a function, but retained the current sequence in a Static variable so that, as long as no other procedures use the VB Rnd function, no digits in the random number sequence will skipped (during the time the workbook is opened).

Function Rand4()
  Dim X As Long
  Static RndNums As String
  Const TargetNumber As Long = 21
  If Len(RndNums) = 0 Then
    RndNums = "XX" & String(5, "+")
    For X = 3 To 7 Step 2
      Mid(RndNums, X) = Int(9 * Rnd) + 1
    Next
  End If
  Do
    RndNums = Mid(RndNums, 3) & "+" & Int(9 * Rnd) + 1
  Loop While Evaluate(RndNums) <> TargetNumber
  Rand4 = Replace(RndNums, "+", "")
End Function

 

[oldbrewer]

if the numbers being generated are "fairly" random, skipping about in the sequence neither makes them better or worse. My own attempts at generating random numbers have included using the first few numbers to create loops of millions and timing the completion of the loop to an accuracy of 0.01 seconds and taking the number in the second decimal place. Running it tens of times I came up with different hot and cold numbers each time. If I ran it a thousand times, and number 8 was the hottest number, is 8 hot, is my process biased in favour of 8, or is it a chance event ?

 

[Cool blue]

Girl Friday is strangely quiet... she sets a nice challenge but does not offer clarification....

Well... It's not Friday yet