Hi, what is the range of each number.
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!
Hi, what is the range of each number.
Φ(𝑘)𝛼Α𝑡
O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste
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...
kevinCode: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
If using a macro is not an option you can put this
into cells A1,B1 and C1 and put thisCode:=(RANDBETWEEN(0,9))
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 numbersCode:=21-SUM(A1:C1)
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
A B C D E F G 1 Press F-9 to get random digits summing to 21 2 one two three four sum 3 4 8 9 0 21 4 5 6 0 7 9 one two three four sum 8 8 0 0 2 2 4 9 3 8 7 5 23 Sheet1
Worksheet Formulas
Cell Formula 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)
-R
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
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.
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.
How so?but as you can see the last 2 numbers generated would be less random than the first
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.
Like this thread? Share it with others