4 Random Numbers that equal the same Total

girlfriday

New Member
Joined
Jan 21, 2014
Messages
6
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi, what is the range of each number.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
How about something like this

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top