How to insert random numbers that equal a certain number

[baha]

Hello, I need to know how to take a number and divide into certain RANDOM numbers that equal the original number. For example:\



20,000 separated into 75 random numbers which the sum of all random 75 numbers equals 20,000.

[whatsup]

Hi baha

Fill:
E1 put your target (20000)
F1 =SUM(B2:B76)
G1 =E1/F1

B2 = RAND()
C2 =INT(B2*$G$1)

Copy B2 the column down to B76, and C2 down to C75, formula for
C76 =E1-SUM(C2:C75)

That will do the trick...

[baha]

Thank you for the reply. I may be doing something wrong.
Let say I want a certain range of numbers to be listed ONLY. Say 85 thru 645. Can this be done?
Let say I want a 75 random number between 85 - 645 to total 20000. Sorry if I'm asking too much.

Thank you
Baha

[whatsup]

Baha, i doubt, you can manage this by formulas only. Of course, excel got the function RANDBETWEEN(), but you will have to test it on your own. There is no possibility to restrict the results to a certain sum, but on trial and error by pressing F9 you may get results.

Otherwise, vba will be a option, the solver as well, but I don't know if that would be fine with you

[baha]

Thanks for your reply. I'll try and see what happens.
Have a wondefull day.

[macropod]

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:

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.

[Bruno Campanini]

In the range of 85 to 645 there are 645-85+1=561 numbers.
If you take the first 21 ones you get a sum of 84*21*22/2 = 19404;
if you get the first 22 ones you get a sum of 84*22*23/2 = 21252;
and so on.
In my opinion there is only one way to get 76 numbers summarizing 20000:
- accepting duplicates by running VBA code.
Is it ok for you?

Bruno

[Bruno Campanini]

Sorry, mis-created series formula:
A1 = 85
r = 1
A76 = a1 + r * (n - 1) = 160
S76 = [n * (n + 2 * a1 -1)] / 2 = 9321

BTW, we must create all combinations of 561 elements taken 76 by 76,
whose number is:
C561,76 > 215385 * 10^90

Mission impossible!

Bruno

[macropod]

Originally Posted by Bruno Campanini

Mission impossible!

You obviously haven't tried the solution I posted...

To show how simple it is to get a series of 75 unique numbers that add to 20000, simply insert:
=INT(317.75-ROW()*4/3)
and copy down to row 75. The sum of the rows will be 20000. Granted, these numbers aren't random, but they also go nowhere near exhausting the possibilities contemplated by the OP.

[Bruno Campanini]

Originally Posted by macropod

You obviously haven't tried the solution I posted...

To show how simple it is to get a series of 75 unique numbers that add to 20000, simply insert:
=INT(317.75-ROW()*4/3)
and copy down to row 75. The sum of the rows will be 20000. Granted, these numbers aren't random, but they also go nowhere near exhausting the possibilities contemplated by the OP.

Well, OP requested 76 random numbers in the range 85 to 645
Your formula gives 75 quasi-successive number in the range 216 to 317.
Honestly you can't consider it neither a solution, nor a pseudo-solution.
Another simpler non-solution is:
Sum 75 successive numbers in the range 227 to 301, plus 200.

The problem is a Linear Programming Problem whose solution can be achieved also with the Simplex Algorithm coded in Excel VBA, but it involves a setup of 561 parameters... which is a very long story.
For sure Mathematica can do the job but it requires either a more complex code or an extraordinary long command line.
Taking into account these difficulties I said Mission Impossible.

Bruno

[whatsup]

Hi again

@Bruno

Well, OP requested 76 random numbers in the range 85 to 645

The second time you got it wrong, it's 75 numbers!!! There is no mentioning that the random numbers may not contain duplicates!!!

Any comment on a perfectly working solution the like "wouldn't work", even by replacing it with a statement like "mission impossible" makes me really cross.
If you don't bother to read the question properly, neither to try out provided solutions, please just keep your wisdoms to yourself.

Offering on this theme a solution involving the simplex algorithm is completely offtopic, you read obviously somewhere something, but can't get loose ends fit.

@Paul
Great solution, efficiently and fast, and quite comfortable when using vba for recalculations!

Cheers

[macropod]

Bruno: You really need to pay attention.

In post #1 the OP clearly said 75 random numbers, not 76.

My reply in post #6 clearly shows how to do that. Granted, I used a different set of boundary numbers, but that is not the point.

My reply to you in post #9 was aimed, not at producing random numbers, but at demonstrating the abject fallacy of your 'impossible!' assertion. The simple fact of the matter is that there is a very large number of possible solutions to the problem, with or without allowing for repeated numbers.

[Bruno Campanini]

[quote=whatsup;73911]Hi again

@Bruno

The second time you got it wrong, it's 75 numbers!!!
***
Ok, my second fault... waiting for the third one.


There is no mentioning that the random numbers may not contain duplicates!!!
***
Don't be ridiculous!
If you allow duplicates the simplest formula is:
74*a + b = 20000
where:
a = Int(20000/74) = 270
b = 20000 - 74*a = 20


Offering on this theme a solution involving the simplex algorithm is completely offtopic, you read obviously somewhere something, but can't get loose ends fit.
***
You say Simplex Algorithm is off-topic because you never wrote such a code in Excel VBA.
I did it and it works fine!

Would you like to see it?
If you do, you must confirm me in advance that you know how Simplex works.

Ciao
Bruno

[Bruno Campanini]

Originally Posted by macropod

Bruno: You really need to pay attention.

In post #1 the OP clearly said 75 random numbers, not 76.

My reply in post #6 clearly shows how to do that. Granted, I used a different set of boundary numbers, but that is not the point.

My reply to you in post #9 was aimed, not at producing random numbers, but at demonstrating the abject fallacy of your 'impossible!' assertion. The simple fact of the matter is that there is a very large number of possible solutions to the problem, with or without allowing for repeated numbers.

Saying "allowing for repeated numbers" is ridiculous, see my previous post.

The only mathematical way to solve the problem is:
a - calculate the more then 33681*10^90 combination C561,75
b - chose among them the ones whose sum is 20000
(may be they are many millions)
c - choose random among those many millions.

I don't know any PC/Program able to run that job. Did you?

A shortcut could be using a Linear Programming Algorithm such as
the Simplex Algorithm, but it requires a very complex preliminary
set up (there are 561 variables!) and I'm not sure to be able to do so.
Then, as far as the correct and complete and Excel-solution is concerned,
I repeat: Mission Impossible!

Practical solutions are possible, even off-Excel, as long as one can
forget the "random" attribute.

Bruno

[macropod]

You are behaving like a troll. To say as you do:

"allowing for repeated numbers" is ridiculous

makes me wonder if you even understand what random numbers are. And, yes, I studied statistics at Uni. Have you ever been in one?

Your assertion that one has to:

calculate the more then 33681*10^90 combination

suggests you only know of the crudest brute-force methods of approach to such problems.

For a solution without repeated numbers, simply add another column, B, with:
B1 =COUNT(A$1:A1)
Copy B1 down to B75
B76 =SUM(B1:B75)
Then, with the macro, change:
While Range("A75").Text = "#NUM!"
to:
While Range("A75").Text = "#NUM!" Or Range("A75").Text < 80 Or Range("A75").Text > 600 Or Range("B76").Value > 75
Just be prepared to wait a long time while Excel does the recalculations. There are, of course, other methods, such as using:
While Range("A75").Text = "#NUM!" Or Range("A75").Text < 80 Or Range("A75").Text > 600
then, when Excel calculates a series with duplicates, change just the duplicates... but I guess the thought never occurred to you that might be possible.

End of story. No "calculate the more then 33681*10^90 combination" required. The fact you don't know how to do this doesn't mean it can't be done - and quite simply - if you know what you're doing.