Generate 10 random numbers that will always have a set average.


I hope I will explain my question clearly.

I want to generate 10 random numbers (idealy between -20 and +20). However, I need those random numbers to average a set figure.

For instance the 10 numbers generated must average 6.95. So Excel might randomly generate 11,14,-6,-12.8,19,-7, 12.6,18.6, 18.1, 2.

I need to be able to repeat the generation.

Probably sounds like a very odd request, but I want to use this to show the effect of volatility on investment returns.




Thanks Don.
ChrisB (rep: 2) Aug 18, '16 at 2:53 am
Add to Discussion


Selected Answer

You want Solver. First install Solver. (this is our tutorial)

Second, run solver.

  1. Data tab > Solver
  2. For Set Objective input the cell that holds the average of the other cells.
  3. On the next line click Value Of and input 6.95
  4. Click Add and then select all of the cells to average and select <= or >= and input 20 and -20 and hit OK for each constraint and then Cancel to get back.
  5. Then hit Solve.

Now this will find a single solution. To make it more dynamic, make one of the 10 cells to average a RAND() or RANDBETWEEN() function and then change 9 cells using Solver and the numbers should change each time you run it.


Answer the Question

You must create an account to use the forum. Create an Account or Login