Excel Random number from a set of options

Question

In MS Excel, how can I randomly sum up to a target number with numbers divisible by 5?

For example, I would like a completely random output of numbers divisible by 5 (5,10,15,20….) in cells B1:B100, to add up to 10000.

I initially looked at the CHOOSE(RANDBETWEEN) option but I can't get up to make the numbers add up to 10000.

Are negative numbers allowed? Do the random numbers need to be uniformly distributed? — jsheeran
– jsheeran, Commented Jan 3, 2023 at 12:32
If the min number is 0, what is the max number allowed? Do you have an idea which logic this could be based on? I don't think that a formula can achieve this. You will need to use something else. If VBA is an option, add the vba tag right below your post. — VBasic2008
– VBasic2008, Commented Jan 3, 2023 at 14:09

Spencer Barnes · Accepted Answer · 2023-01-04 08:27:11Z

1

In Office 365, B1 enter the formula:

=LET(
rndArr,RANDARRAY(100,1),
Correction,INT(SEQUENCE(100,1,1,-1/100)),
INT(rndArr/SUM(rndArr)*2000)*5+IF(Correction=1,10000-SUM(INT(rndArr/SUM(rndArr)*2000)*5),0))

EDIT: the below added in response to the comment about constraining it to a min/max. It's not actually foolproof for all min/max values, but seemed to work well enough for me with the values you supplied.

=LET(
Total, 10000,
Min, 10, Max, 300,
rndArr, RANDARRAY(100, 1),
Correction, SEQUENCE(100, 1, 1, 1) = MATCH(MIN(rndArr), rndArr, 0),
rndArr5, INT(rndArr/SUM(rndArr)*Total/5)*5,
rndArrMinMax, IFS(rndArr5 < Min, Min, rndArr5 > Max, Max, TRUE, rndArr5),
rndArrMinMax + (Total-SUM(rndArrMinMax)) * Correction
)

Explanation of what that does:

Enter Total, Min and Max variables
create rndArr, an array of random numbers (that is the correct size, 100 rows x 1 col)
create Correction, a boolean array of the same size as rndArr where the only TRUE value is the position of the smallest value in rndArr. This is because we'll need to add a figure in later to ensure the total is correct, and want to add it to the smallest number in the array (best possible chance that it won't go above our maximum, remember I said this wasn't foolproof for all values).
create rndArr5, which proportionately increases rndArr so it totals 2000, rounds down to nearest integers, then multiplies by 5. The result is an array of random multiples of 5 that totals somewhere below 10000
create rndArrMinMax by checking rndArr5 (our progress so far) against desired min and max values, editing any outside of our desired range to be the min or max value respectively.
Final output value is that corrected value, plus any difference to make the correct total (that's Total - SUM(rndArrMinMax), which is multiplied by our Correction boolean array so it only gets added on the smallest value in the array. Again, this may result in that smallest value going over the max if the totals are way out and/or the Max is very small, but there's not much you can do about that with random numbers.

edited Jan 4, 2023 at 8:27

answered Jan 3, 2023 at 16:00

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

delicatejolly Over a year ago

Wow, this is much more complex than I imagined. It's brilliant. Thank you so much! Also, is there a way to set minimum/maximum numbers as well in addition to the formula above? (i.e. minimum 10 & max 300)

Spencer Barnes Over a year ago

It's possible, just adds more complexity - I'll update my answer.

delicatejolly Over a year ago

This is just perfect for my needs. Thanks so much for your kind explanation as well! Really appreciate it.

Collectives™ on Stack Overflow

Excel Random number from a set of options

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related