Can this be done in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2067999%22%20slang%3D%22en-US%22%3ECan%20this%20be%20done%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067999%22%20slang%3D%22en-US%22%3E%3CP%3E15%3D%3F%2B%3F%2B%3F%2B%3F%2B%3F%3C%2FP%3E%3CP%3E40%3D%3F%2B%3F%2B%3F%2B%3F%2B%3F%3C%2FP%3E%3CP%3E28%3D%3F%2B%3F%2B%3F%2B%3F%2B%3F%3C%2FP%3E%3CP%3E16%3D%3F%2B%3F%2B%3F%2B%3F%2B%3F%3C%2FP%3E%3CP%3ECan%20Excel%20produce%205%20random%20numbers%20that%20equal%20one%20number%20that%20I%20input%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Record%20Book%20Example.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247025i52A95DA2F713E779%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Record%20Book%20Example.jpg%22%20alt%3D%22Record%20Book%20Example.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2067999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2068491%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20this%20be%20done%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2068491%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931794%22%20target%3D%22_blank%22%3E%40Bobbygotit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20RandomToSum(n%20As%20Long%2C%20total%20As%20Long)%0A%20%20%20%20ReDim%20rv(1%20To%20n%2C%201%20To%201)%20As%20Long%0A%20%20%20%20ReDim%20r(1%20To%20n)%20As%20Double%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20s%20As%20Double%0A%20%20%20%20Dim%20t%20As%20Long%0A%20%20%20%20Dim%20d%20As%20Double%0A%20%20%20%20Dim%20j%20As%20Long%0A%20%20%20%20Randomize%0A%20%20%20%20For%20i%20%3D%201%20To%20n%0A%20%20%20%20%20%20%20%20r(i)%20%3D%20Rnd%0A%20%20%20%20%20%20%20%20s%20%3D%20s%20%2B%20r(i)%0A%20%20%20%20Next%20i%0A%20%20%20%20For%20i%20%3D%201%20To%20n%0A%20%20%20%20%20%20%20%20rv(i%2C%201)%20%3D%20Int(total%20*%20r(i)%20%2F%20s)%0A%20%20%20%20%20%20%20%20t%20%3D%20t%20%2B%20rv(i%2C%201)%0A%20%20%20%20Next%20i%0A%20%20%20%20d%20%3D%20total%20-%20t%0A%20%20%20%20For%20i%20%3D%201%20To%20d%0A%20%20%20%20%20%20%20%20j%20%3D%20Application.RandBetween(1%2C%20n)%0A%20%20%20%20%20%20%20%20rv(j%2C%201)%20%3D%20rv(j%2C%201)%20%2B%201%0A%20%20%20%20Next%20i%0A%20%20%20%20RandomToSum%20%3D%20rv%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESelect%20B5%3AB9.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20and%20confirm%20it%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20into%20an%20array%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DRandomToSum(5%2CB10)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20to%20F5%3AF9%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2069796%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20this%20be%20done%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2069796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931794%22%20target%3D%22_blank%22%3E%40Bobbygotit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20possibility%2C%20if%20you%20turn%20on%20iteration%20(Options%5CFormulas%2C%20check%20iterations%20box%2C%20I%20set%20the%20max%20number%20of%20iterations%20to%201000)%20is%20using%20a%20circular%20reference.%20If%20it%20stops%20at%20the%20max%20number%20of%20iterations%20without%20finding%20a%20solution%2C%20you%20should%20be%20able%20to%20hit%20the%20F9%20key%20until%20it%20does%20(or%20increase%20the%20max%20iterations).%20Note%20-%20it%20appears%20iteration%20may%20need%20to%20be%20re-activated%20whenever%20the%20workbook%20is%20closed%2Freopened.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20note%20that%20if%20you%20force%20the%20cell%20to%20recompute%20(for%20example%2C%20clicking%20in%20the%20formula%20bar%20and%20hitting%20enter)%2C%20then%20the%20formulas%20will%20recalculate%20and%20give%20you%20a%20different%20set.%20So%2C%20you%20may%20want%20to%20copy%2Fpaste%20special%20value%20the%20set%20of%20numbers%20(in%20place%20or%20to%20another%20location)%20after%20you've%20computed%20them%20if%20this%20could%20be%20an%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

15=?+?+?+?+?

40=?+?+?+?+?

28=?+?+?+?+?

16=?+?+?+?+?

Can Excel produce 5 random numbers that equal one number that I input

Record Book Example.jpg

 

2 Replies

@Bobbygotit 

 

Here is a custom VBA function:

Function RandomToSum(n As Long, total As Long)
    ReDim rv(1 To n, 1 To 1) As Long
    ReDim r(1 To n) As Double
    Dim i As Long
    Dim s As Double
    Dim t As Long
    Dim d As Double
    Dim j As Long
    Randomize
    For i = 1 To n
        r(i) = Rnd
        s = s + r(i)
    Next i
    For i = 1 To n
        rv(i, 1) = Int(total * r(i) / s)
        t = t + rv(i, 1)
    Next i
    d = total - t
    For i = 1 To d
        j = Application.RandBetween(1, n)
        rv(j, 1) = rv(j, 1) + 1
    Next i
    RandomToSum = rv
End Function

Select B5:B9.

Enter the following formula and confirm it with Ctrl+Shift+Enter to turn it into an array formula:

 

=RandomToSum(5,B10)

 

Fill to the right to F5:F9

@Bobbygotit 

 

Another possibility, if you turn on iteration (Options\Formulas, check iterations box, I set the max number of iterations to 1000) is using a circular reference. If it stops at the max number of iterations without finding a solution, you should be able to hit the F9 key until it does (or increase the max iterations). Note - it appears iteration may need to be re-activated whenever the workbook is closed/reopened.

 

However, note that if you force the cell to recompute (for example, clicking in the formula bar and hitting enter), then the formulas will recalculate and give you a different set. So, you may want to copy/paste special value the set of numbers (in place or to another location) after you've computed them if this could be an issue.