Thanks:  0
Likes:  0

# Thread: 4 Random Numbers that equal the same Total

1. ## 4 Random Numbers that equal the same Total

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!

2. ## Re: 4 Random Numbers that equal the same Total

Hi, what is the range of each number.

3. ## Re: 4 Random Numbers that equal the same Total

This can't be done without having some constraints on the numbers. If you simply used random digits (0-9) you could end up combinations that would not work (i.e 1+1+1+?=21?) This can be done, but if you want true random numbers, then you will need to generate the first 3 then make sure that their total is greater than 12 and if not, then throw them away and get 3 more. my vba is a bit rusty but something like this should work...

Code:
```Randomize

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer

Do
a = CInt(Rnd * 9)
b = CInt(Rnd * 9)
c = CInt(Rnd * 9)
Loop While (a + b + c < 12)
d = 21 - (a + b + c)

Dim s As String
s = CStr(a) & CStr(b) & CStr(c) & CStr(d)
Range("A1").FormulaR1C1 = s```
kevin

4. ## Re: 4 Random Numbers that equal the same Total

If using a macro is not an option you can put this
Code:
`=(RANDBETWEEN(0,9))`
into cells A1,B1 and C1 and put this
Code:
`=21-SUM(A1:C1)`
into cell D1. If the value of D1 is less than 0 or greater than 9 then press the F9 key for a new set of random numbers
kevin

5. ## Re: 4 Random Numbers that equal the same Total

Here is a way. The cells go down to row 151. You can copy these as far down as you like and then copy and paste as values to the columns on the right. Then use filter to look for sums of 21. Or, if you are just looking for 4 numbers, press F-9 and view them.

Excel 2010
ABCDEFG
1Press F-9 to get random digits summing to 21
2onetwothreefoursum
3489021
4
5
60
79onetwothreefoursum
8800224
9387523

Sheet1

Worksheet Formulas
CellFormula
C3=INDEX(C8:C151,\$A\$8)
D3=INDEX(D8:D151,\$A\$8)
E3=INDEX(E8:E151,\$A\$8)
F3=INDEX(F8:F151,\$A\$8)
G3=SUM(C3:F3)
A8=MATCH(21,G8:G151,0)
C8=RANDBETWEEN(\$A\$6,\$A\$7)
D8=RANDBETWEEN(\$A\$6,\$A\$7)
E8=RANDBETWEEN(\$A\$6,\$A\$7)
F8=RANDBETWEEN(\$A\$6,\$A\$7)
G8=SUM(C8:F8)
C9=RANDBETWEEN(\$A\$6,\$A\$7)
D9=RANDBETWEEN(\$A\$6,\$A\$7)
E9=RANDBETWEEN(\$A\$6,\$A\$7)
F9=RANDBETWEEN(\$A\$6,\$A\$7)
G9=SUM(C9:F9)

6. ## Re: 4 Random Numbers that equal the same Total

Code:
```Dim Total as Double, Rand1 as Double, Rand2 as Double, Rand3 as Double, Rand4 as Double
Dim factor as D

Total = 21

Rand1 = Rnd()
Rand2 = Rnd()
Rand3 = Rnd()
Rand4 = Rnd()

factor = Total / (Rand1 + Rand2 +Rand3 +Rand4)

Rand1 = factor * Rand1
Rand2 = factor * Rand2
Rand3 = factor * Rand3
Rand4 = factor * Rand4

Rand1 = WorksheetFunction.Round(Rand1, 0)
Rand2 = WorksheetFunction.Round(Rand2, 0)
Rand3 = WorksheetFunction.Round(Rand3, 0)
Rand4 = Total - Rand1 - Rand2 -Rand3

MsgBox "Four numbers " & Rand1 & Rand2 & Rand3 & Rand4 & "total " & Total```

7. ## Re: 4 Random Numbers that equal the same Total

ZapperZ"s solution will fail at times. For example if the first 3 number generated would be 9,8,7 the last number would be -3. The formula in C1 could be adjusted to solve this problem, but as you can see the last 2 numbers generated would be less random than the first. The most random method would be to generate the 4 numbers via VBA and discard those that are not equal to 21.

8. ## Re: 4 Random Numbers that equal the same Total

Originally Posted by West Man
ZapperZ"s solution will fail at times. For example if the first 3 number generated would be 9,8,7 the last number would be -3.
The solution in the post#3 handles that by making sure the sum of the first 3 is greater than or equal to 12. With that constraint, the fourth number is is guaranteed to be between 0 and 9. Granted the the solution in post#4 can generate erroneous result, I cover those case with the disclaimer of the F9 key press.

but as you can see the last 2 numbers generated would be less random than the first
How so?

In this problem you can truly only have 3 random numbers; the fourth must be constrained and be such that it equals 21-sum(first3). You can certainly generate 4, but if one of those 4 is not held by the constraint, they are all tossed out.

mickerson's post is by far the best solution, because it guarantees a usable solution with out a loop. Even though he uses 4 random number to generate a scaling factor, it ultimately calculates the fourth number as 21- sum(first3).
kevin

9. ## Re: 4 Random Numbers that equal the same Total

Hi,

Nitpicking - If OPs requirement is 4 positive digits i.e. 1-9 then I am afraid there are flaws with both solutions.

zapperX - The situation WestMan is referring to is where the first three numbers total 21 or greater then your last number will be negative. i.e. You need to loop while (a+b+c)<12 OR (a+b+c)>20
Mike - Mike's code may result in numbers > 9. e.g. 1+1+1+18

As far as I can tell, and I am not a mathematician there are several restrictions implied by the presumed requirements of 4 positive digits i.e. 1-9 such as there can only be a maximum of one 1 i.e. 1+1+9+? so only the first digit can be truly randbetween(1,9)

So without a loop, my workings lead to:-

a = RandBetween(1,9)

b = RandBeween(Max(a,2),9)

c = RandBetween(Max(12-(a+b),1),Min(20-(a+b),9))

d = 21-(a+b+c)

Pretty much not that random, especially if order is important.

Eric.

10. ## Re: 4 Random Numbers that equal the same Total

thanks for the clarification Eric.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•