HELP with the RAND() Function!!!!!!

[denise1082]

For my assignment, I am supposed to create a worksheet that will generate a
random social security number (using the correct social security form of 000-
00-0000) There are supposed to be a billion social security possibilities
and each should be equally likely to be selected in the worksheet. I have no
idea where to start with this assignment!!! Any help whatsoever would be
greatly appreciated!!!! Thanks

 

[Harald Staff]

Hi

Start with finding out how this number is built. Can any position have any
digit, or are there some rules of odd/even numbers, certain numbers
totalling up to something, control digits, ... ?

HTH. Best wishes Harald

 

[denise1082]

Thanks for responding Harald!!!

From reading the assignment question, I get that any position can have any
digit. It didn't say anything about odd/even numbers or certain numbers
totalling up to something. Im sure it's a simple problem but I have no clue!!
!! I don't even know how the social security format would be created using
the RAND function. Thanks again for your help!

 

[VBA Noob]

Format cells in custom format to

###-##-####

then enter this in your cells

=RAND()*(999999999-100000000)+100000000

It appears to work

VBA Noo

 

[denise1082]

Wow!!!! Thanks VBA Noob!!! I have been trying to figure this out for a week
now and it only took you a couple of minutes. It absolutely works!!!!!!!!!!
Thank you so much!!!!!!!!!!!!!

 

[VBA Noob]

Just lucky I guess


VBA Noob

 

[Dana DeLouis]

Just for educational curiosity...

This link is to the Social Security Dept.
This mentions Odd/Even:
http://ssa-custhelp.ssa.gov/cgi-bin/ssa.cfg/php/enduser/std_adp.php?p_faqid=87&

The link in the second paragraph is interesting also.
For example, a number is invalid if it starts with 000.
I Don't see anything that begins with 8 or 9 either, but I may be wrong.

 

[joeu2004]

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())

 

[daddylonglegs]

..... And why write RAND()*899999999+100000000 when RAND()*999999999
would do just as well?

surely those 2 are not equivalent? The first can't return a value
below 100000000, whereas the second could return a value as low as zero

 

[VBA Noob]

F- joeu

 

[joeu2004]

surely those 2 are not equivalent?

You are right.

The first can't return a value below 100000000

..... Which is wrong, by the way, based on the intent.

whereas the second could return a value as low as zero

..... Which I believe is closer to the intent of returning all "one
billion" possibilities. So instead of saying "just as well", I should
have said "more correctly".

 

[Tushar Mehta]

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.

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

 

[JMB]

VBA Noob's formula is in the same format as the example in Excel help.
Surely a "teacher" would not knock someone for using it. In fact, one of my
teachers made me redo an assignment because it was too concise -it was
correct and concise, but somehow missed the point of the exercise.

Since each part has to be at least one, wouldn't this be correct?
=Int(RAND()*(999999999-1010001)+1010001)

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())

 

[JMB]

Nope - it is not correct. I think you are correct that each part would have
to be done separately.

VBA Noob's formula is in the same format as the example in Excel help.
Surely a "teacher" would not knock someone for using it. In fact, one of my
teachers made me redo an assignment because it was too concise -it was
correct and concise, but somehow missed the point of the exercise.

Since each part has to be at least one, wouldn't this be correct?
=Int(RAND()*(999999999-1010001)+1010001)

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())

 

[joeu2004]

Talk about being holier than thou.
Of course, I have never met a *good* teacher who emphasized conciseness over
clarity.

I did not "emphasize conciseness over clarity". In fact, I often
deprecate the many (ab)uses of clever "concise" formulations that,
IMHO, obfuscate clarity -- most notably the over-use of SUMPRODUCT().

I am not interested in trading ad hominen attacks, especially with
someone of your caliber. But I would have serious doubts about anyone
who thinks that int(1e9*rand()) has less "clarity" than
rand()*(999999999-100000000)+100000000 for its purpose, not to mention
ignoring the many errors that I also pointed out.

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.

On the contrary, I have never met a "good" teacher who favored a
250-page tome where 50 pages would do just as well -- be it an essay, a
math solution or a computer program.

You seem to be confusing "conciseness" with slacking off. In fact, it
is usually just the opposite: it often takes a great deal more work
and understanding to develop a concise and still complete solution.
"Everything should be made as simple as possible, but not simpler" --
Einstein.

I have said enough on this point -- too much, in fact. I am just
utterly surprised by your thoughtless comments. I think they do not
reflect your usual high quality. In fact, I am beginning to wonder if
someone hijacked your posting id or I have you confused with someone
else.

 

[Biff]

obfuscate clarity -- most notably the over-use of SUMPRODUCT().

I'm curious. Care to elaborate?

Are you a teacher of Excel?

Biff