Generation of random numbers and sum of those with a condition

[ramana]

Hi every body,

I have three columns A,B&C In the column A Iwill generate random
numbers between 40&50, In the Column B I will generate random numbers
between 35&45, The column C is the sum of Column A&B(i.e. C=A+B) but I
the sum should be between 83&88. I need acondition such that the random
numbers generated in Column A&B Should satisfy the Column C(A+B)
condition sdatisfying the A&B columns condition.

Can anybody help me out regarding this issue.

Thanks and Regards

Ramana

 

[Max]

Assuming cols A and B, from row1 down contain the formulae:
=randbetween(40,50)
=randbetween(35,45)

You could put this in C1, copied down,
to track the results of the randomization in cols A and B:
=IF(AND(SUM(A1:B1)>=83,SUM(A1:B1)<=88),SUM(A1:B1),"")

Rows not evaluating to blanks ("") in col C
would then provide the result lines that you're looking for

 

[ramana]

Hi Max,

Thank you vermuch for the response, It could solve my problem
partially. I wanated to fill the column C fully satisfying the
condition and by changing the random values in the columns A7B.

Thanks & Regards

Ramana

 

[Max]

.. I wanted to fill the column C fully satisfying the condition

and by changing the random values in the columns A & B ..

Perhaps one indirect way to achieve this
while preserving the randomization in cols A & B

Extending the earlier set-up, which is presumed
in Sheet1, within A1:C10 (say)

In Sheet1,
Put in D1: =IF(C1="","",C1+ROW()/10^10)
Copy D1 down to D10

Then, in a new Sheet2, we could
-------------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),
"",INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Copy A1 across to C1, fill down to C10
(cover the same range size as in Sheet1)

Sheet2 will return only the lines from Sheet1
satisfying all the conditions, neatly bunched at the top

Pressing F9 will recalc/regenerate afresh the results in Sheet2

The number of lines showing in Sheet2's cols A to C would vary with each
recalc (each press of F9). So you could recalc until you're happy with the
number of lines generated in Sheet2 (but do note that it may or it may never
reach the full 10 lines in Sheet2), then just freeze the results elsewhere
with a copy > paste special > values > ok

 

[joeu2004]

I have three columns A,B&C In the column A Iwill generate
random numbers between 40&50, In the Column B I will
generate random numbers between 35&45, The column C is
the sum of Column A&B(i.e. C=A+B) but I the sum should
be between 83&88. I need acondition such that the random
numbers generated in Column A&B Should satisfy the Column C(A+B)

First, your requirements are unclear. Must A, B and C
be integers, or can they be non-integer real numbers?
That is a minor nitpick in the scheme of things. But
you should make your requirements clear.

Second, your specifications are inconsistent. If A must
be 40-50 and C (A+B) must be 83-88, then B must be 33-48.
Alternatively, if B must be 35-45 and C must be 83-88,
then A must be 38-53. Or if A must be 40-50 and B must
be 35-45, then C must be 75-95.

Finally, assuming that A and C (A+B) are the strongest
constraints, I would generate random A and random C
(A+B), then compute B = A-C.

For example, assuming that you want integers:
A1: =RANDBETWEEN(40,50)
C1: =RANDBETWEEN(83,88)
B1: =C1-A1

If RANDBETWEEN() is not available, see the Help text
to learn how to install the Analysis Toolpak add-in.
Alternatively, replace RANDBETWEEN(x,y) with
x+INT((y-x+1)*RAND()).

 

[joeu2004]

Finally, assuming that A and C (A+B) are the strongest
constraints, I would generate random A and random C
(A+B), then compute B = A-C.

Of course, I meant B = C-A, as evidenced by the Excel
notation that follows.

 

[joeu2004]

Second, your specifications are inconsistent. If A must
be 40-50 and C (A+B) must be 83-88, then B must be 33-48.
Alternatively, if B must be 35-45 and C must be 83-88,
then A must be 38-53. Or if A must be 40-50 and B must
be 35-45, then C must be 75-95.

I'm sorry: I'm wrong. It appears that you are trying
to select from the following sparse table. Right?

(Sorry if it does not line up well in the posting.)

B
35 36 37 38 39 40 41 42 43 44 45
A 40 83 84 85 C
41 83 84 85 86
42 83 84 85 86 87
43 83 84 85 86 87 88
44 83 84 85 86 87 88
45 83 84 85 86 87 88
46 83 84 85 86 87 88
47 83 84 85 86 87 88
48 83 84 85 86 87 88
49 84 85 86 87 88
50 85 86 87 88

Interesting problem!

 

[joeu2004]

I have three columns A,B&C In the column A Iwill generate
random numbers between 40&50, In the Column B I will
generate random numbers between 35&45, The column C is
the sum of Column A&B(i.e. C=A+B) but I the sum should
be between 83&88. I need acondition such that the random
numbers generated in Column A&B Should satisfy the Column C(A+B)

Okay, I think the following will do what you want:

A1: =RANDBETWEEN(40,50)
B1: =RANDBETWEEN(MAX(35,83-A1),MIN(45,88-A1))
C1: =A1+B1

That is based on the following table that fits your
constraints:

A minB maxB
40 43 45
41 42 45
42 41 45
43 40 45
44 39 44
45 38 43
46 37 42
47 36 41
48 35 40
49 35 39
50 35 38

See the caveats in my previous message regarding integer
v. non-integer requirements and how to install or substitute
for RANDBETWEEN.

 

[ramana]

Hi joeu,

Thank you verymuch showing interest in solving the problem. The
formula you gave at the last session is very useful and came closer to
solve the problem. But the one thing I wanted to refine is I need real
numbers at least with two decimal points. I could get the real numbers
generated by RAND()*(x-y)+y formula but not by RANDBETWEEN formula. By
this I could able to generate real numbers only in the column A and C
but not in B. But I need The column B also should be real numbers not
integers.

Thaks and Best Regards

Ramana

 

[ramana]

Hi Joeu,

In atrial and error method accidentally I got the solution to
generate real random numbers with the randbetween function.

What I did to solve this problem is.

A1: =RAND()*(50-40)+40
B1:
=RANDBETWEEN(MAX(35000,83000-A1*1000),MIN(45000,88000-A1*1000))/1000
C1: =A1+B1


The above is working well and solved my problem Once agai I thank
Jeou&Max for your interest and the solution given by youpeople. which
made my work easier.

Thanks a lot...

Ramana

 

[joeu2004]

But the one thing I wanted to refine is I need real
numbers at least with two decimal points.

.... As I anticipated in my first response.

What I did to solve this problem is.
A1: =RAND()*(50-40)+40
B1:
=RANDBETWEEN(MAX(35000,83000-A1*1000),MIN(45000,88000-A1*1000))/1000
C1: =A1+B1

I would define A1 as ROUND(RAND()*(50-40)+40,2).
That makes the precision consistent for all three
cells, and it has the added benefit of yielding
numbers over the entire range of 40-50.

As for B1, what you have should work. But I would
do a more natural extension of what I wrote
previously, namely (see the RAND() help text):

ROUND(RAND()*(MIN(45,88-A1)-MAX(35,83-A1))+MAX(35,83-A1),2)

That has the added benefit of using RAND(), not
RANDBETWEEN(). I have read that the RNG in the
Data Analysis package does a poor job; RAND() is
better, at least in later revisions of Excel. I
wonder if RANDBETWEEN() uses the RNG in the Data
Analysis package :-(.

(Perhaps an MVP can dismiss my concern.)

 

[ramana]

Hi Joeu,

thank you for the formula.

Thanks & Best Regards

Ramana