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

#### sinivasan

##### New Member
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.
 A B 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?

### Excel Facts

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

#### mikerickson

##### MrExcel MVP
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
Thanks a lot mikerickson!
I got what I wanted, thanks much!

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...