Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

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 appear to be somewhat realistic.

(Common names will appear more often than uncommon names.)

Figure 20-7 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 generated names begin in cell A11.

This workbook, named
name generator.xlsx
, is available on the companion

CD-ROM.

Figure 20-7:
This workbook uses a megaformula to generate realistic random names.

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