Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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" <u24698@uwe skrev i melding news:64029ca54140f@uwe... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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! Harald Staff wrote: 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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
Format cells in custom format to ###-##-#### then enter this in your cells =RAND()*(999999999-100000000)+100000000 It appears to work VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566371 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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 wrote: Format cells in custom format to ###-##-#### then enter this in your cells =RAND()*(999999999-100000000)+100000000 It appears to work VBA Noo |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
Just lucky I guess :) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566371 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
Just for educational curiosity...
This link is to the Social Security Dept. This mentions Odd/Even: http://ssa-custhelp.ssa.gov/cgi-bin/...hp?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. -- HTH. :) Dana DeLouis Windows XP, Office 2003 "denise1082" <u24698@uwe wrote in message news:6402e879b7c65@uwe... 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! Harald Staff wrote: 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 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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()) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
Wrote: ..... 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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566371 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
F- joeu -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=566371 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
daddylonglegs wrote:
Wrote: ..... And why write RAND()*899999999+100000000 when RAND()*999999999 would do just as well? 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". |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
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) " wrote: 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()) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
Nope - it is not correct. I think you are correct that each part would have
to be done separately. "JMB" wrote: 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) " wrote: 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()) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
Tushar Mehta wrote:
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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with the RAND() Function!!!!!!
obfuscate clarity -- most notably the over-use of SUMPRODUCT().
I'm curious. Care to elaborate? Are you a teacher of Excel? Biff wrote in message oups.com... Tushar Mehta wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|