Evenly distribute 100% across a selection of items.

[vanclute]

I would imagine this has been asked many times before as it seems like a somewhat common thing to need to do, but I'll be ****ed if I can find it.

So I have a list of items... could be any number but let's say it's 20 cels for now. I want to randomly assign a percentage to each, such that the total adds up to 100%.

How to accomplish said task in Excel? Anyone have any ideas?

Thanks!

 

[shg]

As written, it will only work with a list of exactly 10 items.

 

[sdruley]

Great work shg. You were all over this one and met the needs extremely well. All I accomplished was making people upset. Not my day.

 

[vanclute]

As written, it will only work with a list of exactly 10 items.

Yep, I've already modified it to work with a list of any arbitrary length (well again, under 100 of course). Thanks again, much appreciated and elegant solution!

 

[mirabeau]

Great work shg. You were all over this one and met the needs extremely well. All I accomplished was making people upset. Not my day.

Steve,

In the event that was in any way headed in my direction then hey, that's no problem to anyone. I knew what you meant, but just had a bit of concern about the accuracy of the way you said it. Would it have been better had I said nothing?

I wish your every day to be a good day.

 

[sdruley]

Steve,

In the event that was in any way headed in my direction then hey, that's no problem to anyone. I knew what you meant, but just had a bit of concern about the accuracy of the way you said it. Would it have been better had I said nothing?

I wish your every day to be a good day.

My wife recently passed away so I have not been myself. But yes, your comments were spot on and well stated. Your original solution was great and I should have left it alone. My mistake was not asking what type of distribution was really needed for the intended application.

Respectfully,

Steve

 

[mirabeau]

My wife recently passed away so I have not been myself. But yes, your comments were spot on and well stated. Your original solution was great and I should have left it alone. My mistake was not asking what type of distribution was really needed for the intended application.

Respectfully,

Steve

Steve,

I don't know you and almost certainly never will, and I can only offer you my condolences. And given this, I deeply regret my remark. But

"The Moving Finger writes: and, having writ,
Moves on: nor all thy Piety nor Wit
Shall lure it back to cancel half a Line,
Nor all thy Tears wash out a Word of it."
from Edward Fitgerald's rendition of Rubaiyat of Omar Khayyam.

(Ignore the following if you like)
Regarding distributions, no type of distribution was specified in the opening post. Just a set of ten random numbers subject to the single linear condition that they sum to 1 (or 100%). Or, if you like, nine random numbers with the tenth being obtained as a residual. Analogy with cutting string by all means if it helps, but how the linear condition is imposed makes no difference to the resulting distribution if the same initial generating distribution is used. Excel Rand() is approx. rectangular, but could easily use others if so specified.