check for value between 101 & 110 same value can't be repeated in
 

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

check for value between 101 & 110 same value can't be repeated in
 

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"

check for value between 101 & 110 same value can't be repeated in
 

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.

check for value between 101 & 110 same value can't be repeated in
 

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"

check for value between 101 & 110 same value can't be repeated
 

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"

check for value between 101 & 110 same value can't be repeated
 

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"

check for value between 101 & 110 same value can't be repeated
 

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"

check for value between 101 & 110 same value can't be repeated
 

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"