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

  • Thread starter Thread starter Jackanorry
  • Start date Start date
J

Jackanorry

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

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 Tools>Options 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(B2>0,COUNTIF($B$1:$B2,B2)=1)),B2,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)
 
Bob & joeu2004 -- thanks for your responses.

Both worked - great stuff.
Thanks again

John
 
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(B2>0,COUNTIF($B$1:$B2,B2)=1)),B2,INT(RANDBETWEEN(101,110)))

Thanks again

John
 
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)
 
Bob,

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

Therefore: 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
 
Back
Top