Forum Discussion
Distribution of one number over different categories with specific probabilities per category
Lowbaer OK, so you want to generate 6 random percentages that add up to 100%.
In older versions of Excel, use =RAND() in 6 cells and then express each of these as a percentage of their sum. Or if you have a modern Excel version that supports dynamic arrays you could use something like below to spill 6 percentages in one go.
=LET( r, RANDARRAY(1, 6), s, SUM(r), TRANSPOSE( INDEX(r, 1, SEQUENCE(6)) / s ) )The attached file contains both solutions.
I missed the possibility of using FREQUENCY!
It turns out that this is somewhat roundabout as a way of generating frequencies! What I did was to generate a possible result that assigns each of 10 objects to a category with the relevant probabilities
= LET(
cumulative, SCAN(0, Probability, SUM),
result, INDEX(Category, XMATCH(RANDARRAY(N),cumulative,1)),
result
)
Only then did it sink in that you wanted to know the frequency of each category, not the result itself.
My solution was to count the occurrences of each category
= LET(
cumulative, SCAN(0, Probability, SUM),
result, INDEX(Category, XMATCH(RANDARRAY(N),cumulative,1)),
counts, MAP(Category, LAMBDA(cat, SUM(SIGN(result=cat)))),
HSTACK(Category, counts)
)
but to go back to the formula that generates the result and calculate frequencies directly is more efficient
= LET(
cumulative, SCAN(0, Probability, SUM),
HSTACK(Category, DROP(FREQUENCY(RANDARRAY(N),cumulative),-1))
)
Note1: The 'cumulative' probability gives the array constant in rachel's formula.
Note2: DROP is used to remove the count of values exceeding 100%
- I was just trying to create an event that happens with 50% probability, 20% probability... etc. Continuous uniform distribution was the first thing that comes to my mind. I even try to google whether RANDARRAY indeed generates N numbers of i.i.d X~U[0, 100].....