Prev: Applying multiple distinct conditional formatting rules
Next: Moving Columns
From: Jackanorry on 6 Jul 2008 17:19
Bob & joeu2004 -- thanks for your responses.

Both worked - great stuff.
Thanks again

John

"Bob Phillips" wrote:

>
> 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)
>
> "Jackanorry" <Jackanorry(a)discussions.microsoft.com> wrote in message
> news:4DBE0E80-9E80-4A06-BCD9-97329ED612CF(a)microsoft.com...
> >
> >
> > :. if a1 equals number between 101 - 110 then a2 can not equal same
> > value -
> > value is determined by "RANDBETWEEN"
>
>
>
From: Jackanorry on 6 Jul 2008 21:23
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



"Bob Phillips" wrote:

>
> 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)
>
> "Jackanorry" <Jackanorry(a)discussions.microsoft.com> wrote in message
> news:4DBE0E80-9E80-4A06-BCD9-97329ED612CF(a)microsoft.com...
> >
> >
> > :. if a1 equals number between 101 - 110 then a2 can not equal same
> > value -
> > value is determined by "RANDBETWEEN"
>
>
>
From: Bob Phillips on 7 Jul 2008 03:55
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" <Jackanorry(a)discussions.microsoft.com> wrote in message
news:3437A490-65B5-4ED1-9FC0-E4BA04100A22(a)microsoft.com...
> 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
>
>
>
> "Bob Phillips" wrote:
>
>>
>> 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)
>>
>> "Jackanorry" <Jackanorry(a)discussions.microsoft.com> wrote in message
>> news:4DBE0E80-9E80-4A06-BCD9-97329ED612CF(a)microsoft.com...
>> >
>> >
>> > :. if a1 equals number between 101 - 110 then a2 can not equal same
>> > value -
>> > value is determined by "RANDBETWEEN"
>>
>>
>>


From: Jackanorry on 9 Jul 2008 09:27
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

"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" <Jackanorry(a)discussions.microsoft.com> wrote in message
> news:3437A490-65B5-4ED1-9FC0-E4BA04100A22(a)microsoft.com...
> > 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
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> >>
> >> 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)
> >>
> >> "Jackanorry" <Jackanorry(a)discussions.microsoft.com> wrote in message
> >> news:4DBE0E80-9E80-4A06-BCD9-97329ED612CF(a)microsoft.com...
> >> >
> >> >
> >> > :. if a1 equals number between 101 - 110 then a2 can not equal same
> >> > value -
> >> > value is determined by "RANDBETWEEN"
> >>
> >>
> >>
>
>
>
 | 
Pages: 1
Prev: Applying multiple distinct conditional formatting rules
Next: Moving Columns