Generate random value

[benny]

Dear All,
In order to get random code label i used RAND function, unfortunately i
could not get the correct value.
What value i want to get is all value greater than 100 and less than 1000
with no number duplicated consecutively for instance 110, 199,111, etc.
Just number like 102, 121 or 123 etc.

What appropriate RAND function i should applied?
Highly appreciate for helping.

regards
\benny

 

[Robert]

=RANDBETWEEN(100,1000)

 

[Robert]

use the =RANDBETWEEN(100,1000) function. To do the rest perhaps
filter by unique values and do a sort?

But then it isn't really random if you want 900 consecutive numbers in
order?

 

[benny]

Thanks you in advance,
I did it, when i hit F9 to refresh the new random value i've still got the
value with 2 numbers duplicated i.e 889, 566...they are the unwish value.

cheers

 

[Dave Peterson]

Maybe you could use J.E. McGimpsey's =RandInt() User Defined Function:
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[kcc]

A trick I have seen posted before is to put the values from
101 to 999 (or 100 to 1000) in column A and rand() in
column B next to each value. After B calculates, paste
value to eliminate the formulas. Finally, sort column A and
B using B as the key. Column A will then be in random order.
B could have duplicates, but with 900 values and 15 digits,
it is very unlikely.
kcc