Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
How about =A1+RANDBETWEEN(0,19)
best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Karin" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
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 "Karin" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
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,maxsu m-A1)) Guess I should've thought it through a little more before posting... : ) On Jan 6, 11:35*am, "Bernard Liengme" wrote: How about =A1+RANDBETWEEN(0,19) best wishes -- Bernard Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme "Karin" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
Thanks Bernie!! Like yours much better than the mammoth one I just
figured out! On Jan 6, 11:49*am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
"Karin" wrote:
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 ----- "Karin" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
On Jan 6, 12:32*pm, "Joe User" <joeu2004 wrote:
"Karin" wrote: 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. 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!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
On Wed, 6 Jan 2010 09:27:01 -0800, Karin
wrote: 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 C1:D1 =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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
random integer with sum constraint
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 On Wed, 6 Jan 2010 12:49:45 -0500, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 "Karin" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add an integer to an existing integer? | Excel Worksheet Functions | |||
Random Integer | New Users to Excel | |||
Adding a constraint. | Excel Discussion (Misc queries) | |||
cell value constraint | Excel Discussion (Misc queries) | |||
Definition of integer constraint | New Users to Excel |