:. if a1 equals number between 101 - 110 then a2 can not equal same value -
value is determined by "RANDBETWEEN"

value is determined by "RANDBETWEEN"

Which value, A1 or A2?

--
Biff
Microsoft Excel MVP


"Jackanorry" wrote in message
...


:. if a1 equals number between 101 - 110 then a2 can not equal same
value -
value is determined by "RANDBETWEEN"

On Jul 2, 8:49*pm, Jackanorry
wrote:
if a1 equals number between 101 - 110 then a2 can not
equal same value - value is determined by "RANDBETWEEN"

If you are trying to generate 2 unique random numbers between 101 and
110, perhaps the following will work for you.

In some column (e.g. Z21:Z30), put the formula =RAND() into Z21 and
copy down through Z30. If you do not want the numbers to change
(every time you modify any part of the worksheet!), you can copy-and-
paste-special-value over Z21:Z30.

In A1, put the following formula and copy it into A2:

=100 + RANK(Z21,$Z$21:$Z$30)

RANK() returns in the ordinal position (1 through 10) of the value in
Z21 (and Z22 in A2) among the random values in Z21:Z30.

First, ensure cell A1 is empty and goto ToolsOptions and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))
it should show a 0.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B2, don't change it,
just edit to reset to 0, and re-input A1.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jackanorry" wrote in message
...


:. if a1 equals number between 101 - 110 then a2 can not equal same
value -
value is determined by "RANDBETWEEN"

Bob & joeu2004 -- thanks for your responses.

Both worked - great stuff.
Thanks again

John

"Bob Phillips" wrote:


First, ensure cell A1 is empty and goto ToolsOptions and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))
it should show a 0.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B2, don't change it,
just edit to reset to 0, and re-input A1.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jackanorry" wrote in message
...


:. if a1 equals number between 101 - 110 then a2 can not equal same
value -
value is determined by "RANDBETWEEN"

Darn,
I got a little ahead of myself.
While the formulae do work, there's a 'glitch' of sorts in that on each line
(consists of up to 7 numbers) there is atleast one number that is repeated -
see below.
101 109 109 110 104 101 103

Here's the formula Bob provided
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))

Thanks again

John



"Bob Phillips" wrote:


First, ensure cell A1 is empty and goto ToolsOptions and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))
it should show a 0.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B2, don't change it,
just edit to reset to 0, and re-input A1.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jackanorry" wrote in message
...


:. if a1 equals number between 101 - 110 then a2 can not equal same
value -
value is determined by "RANDBETWEEN"

I am not understanding. IN your original post you said that you would have
one number between 101 and 110, and you want another that was not equal to.

Where do the 7 numbers in a line now come into it? What exactly is the
requirement?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jackanorry" wrote in message
...
Darn,
I got a little ahead of myself.
While the formulae do work, there's a 'glitch' of sorts in that on each
line
(consists of up to 7 numbers) there is atleast one number that is
repeated -
see below.
101 109 109 110 104 101 103

Here's the formula Bob provided
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))

Thanks again

John



"Bob Phillips" wrote:


First, ensure cell A1 is empty and goto ToolsOptions and on the
Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))
it should show a 0.

Finally, put some value in A1, say an 'x', and all the random numbers
will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B2, don't change it,
just edit to reset to 0, and re-input A1.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jackanorry" wrote in message
...


:. if a1 equals number between 101 - 110 then a2 can not equal same
value -
value is determined by "RANDBETWEEN"

Bob,

The requirement is generate (randomly) up 7 values between 101 - 110 in a
row. No value can be repeated in the row.

Therefo 101 109 109 110 104 101 103 : is not working for me as you can
see in this row 2 values were repeated.

The formula would then be used to generate values between the same criteria
in following rows : up to 100 rows where repeated values from the row above
would be ok.

Hope this helps - and thanks again Bob for offering your experience and
knowledge.

John

"Bob Phillips" wrote:

I am not understanding. IN your original post you said that you would have
one number between 101 and 110, and you want another that was not equal to.

Where do the 7 numbers in a line now come into it? What exactly is the
requirement?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jackanorry" wrote in message
...
Darn,
I got a little ahead of myself.
While the formulae do work, there's a 'glitch' of sorts in that on each
line
(consists of up to 7 numbers) there is atleast one number that is
repeated -
see below.
101 109 109 110 104 101 103

Here's the formula Bob provided
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))

Thanks again

John



"Bob Phillips" wrote:


First, ensure cell A1 is empty and goto ToolsOptions and on the
Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B20,COUNTIF($B$1:$B2,B2)=1)),B 2,INT(RANDBETWEEN(101,110)))
it should show a 0.

Finally, put some value in A1, say an 'x', and all the random numbers
will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B2, don't change it,
just edit to reset to 0, and re-input A1.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jackanorry" wrote in message
...


:. if a1 equals number between 101 - 110 then a2 can not equal same
value -
value is determined by "RANDBETWEEN"