Forum Discussion
Solved
Distribution of one number over different categories with specific probabilities per category
Hi all,
I've been trying to create a formula to distribute a given amount of points over multiple different categories with different probabilities for each individual category.
So for example 10 points over 6 different categories with 50% probability for each point to be added to category 1, 20% for category 2, 10% for categories 3 and 4, and 5% for categories 5 and 6.
I appreciate any ideas!
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.
8 Replies
Lowbaer Taking a wild guess now, but perhaps something like this?
Formula was B3 dragged across to G3. Can be dragged down if needed.
Thanks for the idea! It would work perfectly to distribute the points over the categories at different proportions, but it lacks the element of randomness. I'd love to have some sort of random generator, which distributes the points over the categories with different probabilities per category. I've tried to play with if, rand and rank formulas but couldn't get anything to work that way.
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.
