Skip navigation

help with my formula please

219 Views 3 Replies Latest reply: Jan 20, 2013 6:17 AM by shaneyshears RSS
shaneyshears Level 1 Level 1 (0 points)
Currently Being Moderated
Jan 20, 2013 5:21 AM

i want to generate 5 random numbers in 5 different cells after giving another cell a value example cell a6=1000 i need cells a1 to a 5 add up to the value of a6 but be random numbers like 232 453 ect please help what function could i use

numbers
  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Jan 20, 2013 6:02 AM (in response to shaneyshears)

    Shaney,

     

    Here's a general solution that will work for any number of figures summing to another figure...

     

    Screen Shot 2013-01-20 at 9.01.49 am.png

    The formula in the cells with the random numbers is:

     

    =RANDBETWEEN(0, A$7-SUM(OFFSET($A$1, 0,0,ROW()-1)))

     

    I hope this meets your needs.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Jan 20, 2013 6:16 AM (in response to Jerrold Green1)

    Thanks for the Solution mark, but I discovered a mistake in my approach. The last of the "random" numbers must be constrained to be the difference between the sum of the preceeding random numbers and the total. My revised, hopefully correct, version is this:

     

    Screen Shot 2013-01-20 at 9.13.45 am.png

    The figure in cell A6, 63 in the example, is the constrained one.

     

    The formula for the A6 is:

     

    =A7-SUM(A$2:A5)

     

    Sorry about the false start.

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.