Microsoft Office Tutorials and References
In Depth Information
Figure 21-7: Using a megaformula to determine the validity of credit card numbers.
Generating random names
The final example is a useful application that generates random names. It uses three name lists, compiled by the
U.S. Census Bureau: 4,275 female first names; 1,219 male first names; and 18,839 last names. The names are
sorted by frequency of occurrence. The megaformula selects random names such that more frequently occurring
names have a higher probability of being selected. Therefore, if you create a list of random names, they will ap-
pear to be somewhat realistic. (Common names will appear more often than uncommon names.)
Figure 21-8 shows the workbook. Cells B7 and B8 contain values that determine the probability that the random
name is a male as well as the probability that the random name contains a middle initial. The randomly gener-
ated names begin in cell A11.
This workbook, named name generator.xlsx, is available at this topic's website.
The megaformula is as follows (the workbook uses several names):
=IF(RAND()<=PctMale,INDEX(MaleNames,MATCH(RAND(),
MaleProbability,–1)),INDEX(FemaleNames,MATCH(RAND(),
FemaleProbability,–1)))&IF(RAND()<=PctMiddle,” “&
INDEX(MiddleInitials,MATCH(RAND(),MiddleProbability,–1))&
“.”,””)&” “&INDEX(LastNames,MATCH(RAND(),LastProbability,–1))
I don't list the intermediate formulas here, but you can examine them by opening the file at this topic's website.
Search JabSto ::




Custom Search