random integer with sum constraint

[Karin]

Hi

I want to generate two random integers that fall within a certain range
("min" to "max", e.g. 0 to 10) AND that sum to equal no more than a given
value ("maxsum", e.g. 19)

The first integer is easy (using a Analysis Toolpak add-in)
A1=RANDBETWEEN(min, max)

But that second integer has me stuck. I started with A2=RANDBETWEEN(min,
maxsum-A1) but then a lot of the results were larger than "max." Oops. Any
ideas on what else I need to put in there?

Thanks,
Karin

 

[Bernard Liengme]

How about =A1+RANDBETWEEN(0,19)
best wishes

 

[Bernie Deitrick]

Karin,

In A2, use

=RANDBETWEEN(Min,MIN(Max,MaxSum-A1))

I have assumed you have cells named Max, Min, and MaxSum....

HTH,
Bernie
MS Excel MVP

 

[Schwammy]

Thanks for replying, Bernard; however, the function you gave does not
give me an integer in the desired range of 0 to 10.

that's where my problem lies.

perhaps I should have used a different sample value for my "maxsum".
15 instead of 19.

in A1 and A2, I want to generate two integers between 0 and 10. but
their total can't exceed "maxsum", let's say 15.

So if A1 is randomly generated to be 3, then A2 can be between 0-10.
But if A1 is randomly set to 8, then A2 can only be 0-7.

Wait... writing out the situations above showed me what I had to do:
=IF(maxsum-A1>=max,RANDBETWEEN(min,max),RANDBETWEEN(min,maxsum-A1))

Guess I should've thought it through a little more before
posting... : )

 

[Schwammy]

Thanks Bernie!! Like yours much better than the mammoth one I just
figured out!

 

[Joe User]

I want to generate two random integers that fall within a certain range
("min" to "max", e.g. 0 to 10) AND that sum to equal no more than a
given value ("maxsum", e.g. 19)

The first integer is easy (using a Analysis Toolpak add-in)
A1=RANDBETWEEN(min, max)

That presumes min+max <= maxsum. If that assumption is valid in your case,
fine. Otherwise, I think you should write:

A1: =RANDBETWEEN(min, MIN(max, maxsum-min))

Example: if min=1, max=1000 and maxsum=50, random integers from 50 through
1000 are not valid.

But that second integer has me stuck. I started with
A2=RANDBETWEEN(min, maxsum-A1)

That should be:

A2: =RANDBETWEEN(min, MIN(max,maxsum-A1))


----- original message -----

 

[Schwammy]

That presumes min+max <= maxsum.  If that assumption is valid in yourcase,
fine.  Otherwise, I think you should write:

A1:  =RANDBETWEEN(min, MIN(max, maxsum-min))

Example:  if min=1, max=1000 and maxsum=50, random integers from 50 through
1000 are not valid.

Good point. I hadn't thought of that! Since I'm not sure what the
values will always be, I'll update my "easy" first integer. Thanks,
Joe!!!

 

[Lars-Åke Aspelin]

Hi

I want to generate two random integers that fall within a certain range
("min" to "max", e.g. 0 to 10) AND that sum to equal no more than a given
value ("maxsum", e.g. 19)

The first integer is easy (using a Analysis Toolpak add-in)
A1=RANDBETWEEN(min, max)

But that second integer has me stuck. I started with A2=RANDBETWEEN(min,
maxsum-A1) but then a lot of the results were larger than "max." Oops. Any
ideas on what else I need to put in there?

Thanks,
Karin

You have to be careful when you use the word random.
Which are your requirements on randomness?

One interpretation of "random" is that each valid result should have
the same probability, 1/N where N is the number of valid results.

The proposed formulas, so far, does not fulfil this requirement.
As soon as you restrict the second number based on the value of the
first number, you have lost some kind of randomness.

If you don't have this requirement there are some very simple
"solutions" to your problem that you probably don't like, e.g.

A1= min,
A2 = max
or
A1 = RANDBETWEEN(min, maxsum/2),
A2 = RANDBETWEEN(min, maxsum/2)

If you have the requirement that two numbers, x and y, should each
have an integer value from min to max, but their sum should never
exceed maxsum, there is a reduction of the number of valid results N,
from N = (max-min+1)^2 to
N = (max-min+1)^2- (maxsum-max-min)*(maxsum-max-min+1)/2

If min = 0, max = 10 and maxsum = 15
then N = (10-0+1)^2 - (15-10-0)*(15-10-0+1)/2 = 106
(compared to 121 if there was no maxsum restriction)

If you do have the requirement that each of these 106 result should
have the same probability, then one way to generate the numbers would
be to list all 106 valid combinations of (x,y) in the range A1:B106.
(this could be done manually or with formulas)

Then you use the following array formula in cell C11

=INDEX(Á1:B106;RANDBETWEEN(1;106);COLUMN(A:B))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

This may be perfectly OK, we don't know what these "random" integers
should be used for, but a side effect of letting the result of A1
influence the result of A2 is that there is not the same probability
distribution for A2 as for A1.

Example with min=0, max=10, maxsum=15:
The probability for A1 being 10 is 1/11, more than 9%,
the same for all values from 0 to 10, Average is 5.
but the probability for A2 being 10 is just 6/121, less than 5%, and
not the same for all values from 0 to 10. Average is 4.3181818...

So if A1 and A2 is to be used for some kind of gambling, my bet would
be on A1 if this formula is used to generate the "random" numbers.

Lars-Åke