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.

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

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

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

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

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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

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

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

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

You are behaving like a troll. To say as you do:
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:
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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

@Bruno
That's becoming quite funny now

< No Comment >

The Simplex algorithm is designed for Optimization - what do you want to optimize? You find a perfectly working solution above.

No, thanks

[quote=macropod;73963]You are behaving like a troll. To say as you do:

makes me wonder if you even understand what random numbers are.
***
Ok, I understand what you mean: random doesn't mean unique.
If you have 100 number in a box and you make 2 series of 5
extractions without taking away the extracted ones, you can have
12 3 56 82 1 or 1 1 1 1 1 with the same probability (1/100)^4
and both the extractions are random.
Does it agree with your studies?
Once more: does it agrees with your studies being
C561,75 =
33681682437868788425248026209559563292643307254988 231295607092759280783632985148686348180517440
???

And, yes, I studied statistics at Uni. Have you ever been in one?
***
Yes of course, I studied economics (which comprise statistics)
at University of Bologna - Alma Mater Studiorum A.D. 1088,
the oldest university in the world.
Bologna is in Italy.

Do you really think it is so amazing being graduate?

Bruno