My Random Password Generator

[NeoMoses]

I've seen a few questions asking about random password generation, so I decided to try my hand at it. The entire sheet wouldn't fit, but I think this will get my point across. It uses no VBA, so a simple F9 will recalculate the sheet and give you all new passwords. Enjoy!

password generator.xls
	B	C	D	E	F	G	H	I	J
1	only lower case	pbqg	4 Digit			Character Table
2		uatfu	5 Digit			Upper	Lower	Number	Special
3		gfkqua	6 Digit			B	s	5	*
4		xsobifa	7 Digit			H	q	9	*
5		ddsqguis	8 Digit			Q	x	2	$
6		rgpbquorq	9 Digit			B	q	0	"
7		pwqqwpoisi	10 Digit			S	u	1	(
8	Date	5/6/2005 7:47				O	q	2	#
9	Lower + Upper	oqik	4 Digit			B	k	3	%
10		bpTTQ	5 Digit			H	q	3	&
11		IBuHtD	6 Digit			I	m	8	*
12		AhsDIgS	7 Digit			T	f	4	!
13		BgfhJTQf	8 Digit			Z	f	9	!
14		pBkHiBiEI	9 Digit			A	i	0	"
15		qZdpMbaxgE	10 Digit			P	r	9	#
Password Generator 1.0

 

[erik.van.geit]

MTBer,

thank you for the link
just curious: why use 26 cells if it can be done with one formula ?
=IF(RAND()>52/62,CHAR(INT(RAND()*10+48)),CHAR(INT(RAND()*26+65)+INT(RAND()*2)*32))
this is the longest formula: generating numbers, or lcase or ucase letters: so 62 possible characters randomly choosen


Erik
EDIT: changed formula (57-48+1) ==> 10, etcetera (I pasted from the wrong post)

 

[PA HS Teacher]

Here's a single cell formula that generates a lower case letter 26/62 of the time.
an upper case letter 26/62 of the time,
and a number 10/62 of the time.

63 because 26 letters plus 26 letters + 10 numbers = 62. You could adjust the probability of each to suit your needs.

Concatenate for as many characters as desired.

Note: Analysis tool pack is not required.

=CHOOSE(MATCH(RAND(),{0,0.16,0.58}),
CHAR(INT((57+1-48)*RAND()+48-1)+1),
CHAR(INT((90+1-65)*RAND()+65-1)+1),
CHAR(INT((122+1-97)*RAND()+97-1)+1))

[Edit] Just for fun I pasted the function down 10,000 rows
and calculated the percentage of the time each character came up.

The average rate of occurence for the numbers was:
stdev of 0.001412012 average of 0.01596

Upper Case Letters
stdev of 0.001964344 average of 0.032323077

Lower Case Letters
stdev 0.001964344 average 0.032323077 (interesting exactly the same as for upper?)

 

[erik.van.geit]

Hi, Teacher,

How do you count the lcase and ucase ?
A
a
a
A

=countif(range,"a") gives me 4
=countif(range,"A") gives me 4

kind regards,
Erik

 

[PA HS Teacher]

Good thinking Erik,
That's why I was getting exactly the same statistics. The count wasn't differenciating between the two. (the upper and lower case were produced correctly, but to differenciate when I counted upper and lower I should have used the Exact() function. Don't have time to play now. Keep up the good work!

 

[erik.van.geit]

this is the formula to use

=SUMPRODUCT(--EXACT(A1:A5,B1))

perhaps easy for the experts, for me it was 15 minutes of research and experimenting

kind regards,
Erik

 

[Emily]

Please have a look on the following created by Chinese Excel lover

1:
=CHAR(LARGE({0,33,65}+ROW(32:57),INT(RAND()*62)+1))

2:
{=CHAR(LARGE({0,33,65}+ROW(32:57),INT(RAND()*62)+1))}

 

[PA HS Teacher]

Just saw that post:

http://www.mrexcel.com/board2/viewtopic.php?t=156081&start=30

Outstanding!

But, I would include the $ in the Row function. This was you can paste the formula down as needed.

{=CHAR(LARGE({0,33,65}+ROW($32:$57),INT(RAND()*62)+1))}

 

[apolloh]

5-7 Password generator,it's up to you to decide which characters to disappear in the password.

Take a look at
http://www.ozgrid.com/forum/showthread.php?t=37267