Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

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.

excel
MS Office Version: 2016
posted by: ChrisB (rep: 2) on Wed Aug 17, 2016 at 4:58 am
Answer
Discuss

Discussion

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

Answers

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

posted by: don (rep: 1989) on Wed Aug 17, 2016 at 11:28 am
Discuss


Answer the Question

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