Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 40

Thread: 4 Random Numbers that equal the same Total

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool 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. #2
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,840
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Random Numbers that equal the same Total

    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

  3. #3
    New Member zapperX's Avatar
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member zapperX's Avatar
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Nov 2006
    Posts
    414
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

    -R

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,259
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    10 Thread(s)

    Default Re: 4 Random Numbers that equal the same Total

    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

  7. #7
    Board Regular West Man's Avatar
    Join Date
    Mar 2006
    Location
    Nebraska
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member zapperX's Avatar
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Random Numbers that equal the same Total

    Quote Originally Posted by West Man View Post
    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. #9
    Board Regular
    Join Date
    Dec 2007
    Posts
    307
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    New Member zapperX's Avatar
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Random Numbers that equal the same Total

    thanks for the clarification Eric.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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