How to insert random numbers that equal a certain number

macropod · #8 11-18-2014, 05:34 PM

One possibility:
In A1 =INT(RANDBETWEEN(80,600))
In A2 =INT(RANDBETWEEN(80,MIN(600,(A$76-SUM(A$1:A1))/COUNT(A1,A$76))))
Copy A2 down to A74
In A75 =A76-SUM(A1:A74)
In A76 20000

Pressing F9 will eventually get a series of numbers in rows 1-75 that total the 20000. You might have to press F9 many times to fill all numbers. Or you could use a macro to do the re-calcs for you:

Code:

Sub Recalc()
Application.Calculate
While Range("A75").Text = "#NUM!"
  Application.Calculate
Wend
End Sub

The final number may sometimes be outside the upper & lower bounds (i.e. 80 & 600). You can prevent that by using:
While Range("A75").Text = "#NUM!" Or Range("A75").Text < 80 Or Range("A75").Text > 600
instead of:
While Range("A75").Text = "#NUM!"
This change means it will likely take Excel more iterations (i.e. longer) to return a result. Likewise, the larger you make the '600' in the formulae (and macro), the longer it will take Excel to return a result. Conversely, making the 80 smaller will reduce the time it will take Excel to return a result.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Filling cells with random numbers using loops? (VBA)	Sajj	Excel Programming	4	04-10-2013 02:29 PM
Random number difficulties	ahrinn	Word	12	08-01-2012 01:04 PM
Random number generator for Powerpoint 2011	kala85	PowerPoint	0	11-19-2011 06:16 AM
How to fill a cell with random numbers to do calculations?	zanat0s	Excel	1	06-13-2011 10:33 AM
Display Random Numbers as Transition	greenbutton	PowerPoint	0	03-09-2011 04:43 PM