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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ramana" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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 wrote:
> 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "ramana" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

> .. 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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ramana" <[email protected]> wrote in message
news:[email protected]...
> Hi Max,
>
> Thank you vermuch for the response, It could solve my problem
> partially. I wanted to fill the column C fully satisfying the
> condition and by changing the random values in the columns A & B.
>
> Thanks & Regards
>
> Ramana

[[email protected]]

ramana wrote:
> 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()).

[[email protected]]

I wrote:
> 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.

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

[[email protected]]

I wrote:
> 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!

[[email protected]]

ramana wrote:
> 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

[email protected] wrote:
> ramana wrote:
> > 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,

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

[[email protected]]

ramana wrote:
> 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.

Later ramana wrote:
> 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

[email protected] wrote:
> ramana wrote:
> > 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.
>
> Later ramana wrote:
> > 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.)