# 4 Random Numbers that equal the same Total

#### girlfriday

##### New Member
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!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi, what is the range of each number.

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

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

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
Cell Formulas
RangeFormula
C3=INDEX(C8:C151,\$A\$8)
C8=RANDBETWEEN(\$A\$6,\$A\$7)
C9=RANDBETWEEN(\$A\$6,\$A\$7)
D3=INDEX(D8:D151,\$A\$8)
D8=RANDBETWEEN(\$A\$6,\$A\$7)
D9=RANDBETWEEN(\$A\$6,\$A\$7)
E3=INDEX(E8:E151,\$A\$8)
E8=RANDBETWEEN(\$A\$6,\$A\$7)
E9=RANDBETWEEN(\$A\$6,\$A\$7)
F3=INDEX(F8:F151,\$A\$8)
F8=RANDBETWEEN(\$A\$6,\$A\$7)
F9=RANDBETWEEN(\$A\$6,\$A\$7)
G3=SUM(C3:F3)
G8=SUM(C8:F8)
G9=SUM(C9:F9)
A8=MATCH(21,G8:G151,0)

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``````

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.

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

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.

thanks for the clarification Eric.

Replies
15
Views
298
Replies
6
Views
328
Replies
16
Views
450
Replies
3
Views
164
Replies
6
Views
372

### Forum statistics

1,196,185
Messages
6,013,922
Members
441,795
Latest member
Lilium ### 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.

### Which adblocker are you using?    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

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