Talk about being holier than thou.

Of course, I have never met a *good* teacher who emphasized conciseness over
clarity.

In fact, every time I had a teacher who liked "concise" answers I *knew* I'd
get a good grade without doing a commensurate amount of work.

In article .com, joeu2004
@hotmail.com says...
denise1082 wrote:
VBA Noob wrote:
Format cells in custom format to
###-##-####
then enter this in your cells
=RAND()*(999999999-100000000)+100000000

Wow!!!! [....] It absolutely works!!!!!!!!!!

To a degree, yes. But in my class, that formula would earn you only a
C -- perhaps less. It is not enough to get it "right". It should also
be concise. And by the way, technically that formula and format are
not right.

First, the custom format ###-##-#### fails to display leading zeros.
So the SSN 001-23-4567 is displayed as 1-23-4567. Perhaps "VBA Noob"
meant to write 00#-##-###; or he could have selected the predefined
Custom format 000-00-0000, available in Excel 2003 at least. The
latter is actually the format Special Social Security Number, at
least in Excel 2003.

Second, why write "999999999-100000000" when 899999999 would do just as
well? And why write RAND()*899999999+100000000 when RAND()*999999999
would do just as well? Finally, the above formula can result in
underlying values like 123456789.4. Imagine your suprise when a
subsequent assignment asks you to count the number of SSNs that are
equal to 123456789, and you count zero(!).

The more correct and more concise way to write the above formula is:

=int(1e9*rand())

I write 1e9 instead of 1000000000 because 1e9 is less error-prone. You
can write it either way.

I suspect that yields the result that your assignment asks for, since
you indicated that the assignment says there are "a billion" possible
SSNs. But in my class, you would get an A+ if you provided the answer
I asked for __and__ the answer to the more correct problem statement,
duly noted.

There really are not "a billion" possible SSNs; only about 989 million
-- 988,911,099 to be exact. As someone else pointed out, the reason is
that for a valid SSN, the first part ("area" number) can be only
001-999, the second part ("group" number) can be only 01-99, and the
third part can be only 0001-9999. In other words, zero is not valid in
any component of the SSN.

(It might also be noted that not all "area" numbers and not all
combinations of "area" and "group" numbers are used today. If you were
a criminal, you would do well to pay close attention to that
limitation. But I think it would be acceptable to relegate that fact
to a footnote and otherwise ignore it for the purpose of this
assignment.)

There are several ways to generate a random SSN within those
constraints. One way is:

=1000000*int(1+999*rand()) + 10000*int(1+99*rand()) +
int(1+9999*rand())



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions