How to Create list of random numbers which gives specific total value?

sinivasan

New Member
Joined
Jul 14, 2014
Messages
6
How to get specific total as sum of randomly(using randbetween/rand()) generated values?

I always want to get specific value as the sum of randomly generated values

For example I have sheet as shown below.
AB
1 =RANDBETWEEN(5,10)5
2 =RANDBETWEEN(5,10)6
3 =RANDBETWEEN(5,10)7
4 =RANDBETWEEN(5,10)6
5 =RANDBETWEEN(5,10)9
6 =RANDBETWEEN(5,10)7
7 =RANDBETWEEN(5,10)10
8 =RANDBETWEEN(5,10)5
9 =RANDBETWEEN(5,10)6
10 =RANDBETWEEN(5,10)9
11 =SUM(B1:B10)70

<tbody>
</tbody>

From the above sheet(table) I want to get specific value as the sum of value in Column B(B1:B10 contains =RANDBETWEEN(5,10))

If I want to get 92 as my sum of randomly generated value the excel should populate the random values in column B which gives sum of 92.

How do I do it?
Could someone help me please!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,619
Put =RAND() in some cells (A1:A10) for example
Put your target value, 92 in B1

put =A1*$B$1/SUM($A$1:$A$10) in C1 and drag down to C10.

C1:C10 are your random numbers that sum to 92.
 

sinivasan

New Member
Joined
Jul 14, 2014
Messages
6
Thanks a lot mikerickson!
I really appreciate your contribution.
I got what I wanted, thanks much!:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,102,153
Messages
5,485,059
Members
407,480
Latest member
breederbulldog

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top