Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

3.
Replace the two references to range D5:D20 with the array formula contained in D5:20.

Doing so results in the following array formula in cell G1:

{=IF(MOD(SUM(INT((B5:B20*C5:C20/10)+MOD((B5:B20*C5:C20),

10))),10)=0,”VALID”,”INVALID”)}

4.
Replace the references to cell C5:C20 with the array formula in C5:C20.

Note that you must have a set of parentheses around the copied formula text. The result

is as follows:

{=IF(MOD(SUM(INT((B5:B20*(MOD(ROW(INDIRECT(“1:16”)),

2)+1)/10)+MOD((B5:B20*(MOD(ROW(INDIRECT(“1:16”)),2)+1)),

10))),10)=0,”VALID”,”INVALID”)}

5.
Replacing the references to B5:B20 with the array formula contained in B5:B20 yields the

following:

{=IF(MOD(SUM(INT((MID($F$2,A5:A20,1)*(MOD(ROW(INDIRECT

(“1:16”)),2)+1)/10)+MOD((MID($F$2,A5:A20,1)*(MOD(ROW

(INDIRECT(“1:16”)),2)+1)),10))),10)

=0,”VALID”,”INVALID”)}

6.
Substitute the array formula in range A5:A20 for the references to that range.

The resulting array formula is as follows:

{=IF(MOD(SUM(INT((MID($F$2,ROW(INDIRECT(“1:16”)),1)*(MOD(ROW

(INDIRECT(“1:16”)),2)+1)/10)+MOD((MID($F$2,ROW(INDIRECT

(“1:16”)),1)*(MOD(ROW(INDIRECT(“1:16”)),2)+1)),10))),10)=0,

“VALID”,”INVALID”)}

7.
Substitute the formula in cell F2 for the two references to cell F2.

After making the substitutions, the formula is as follows:

{=IF(MOD(SUM(INT((MID(REPT(“0”,16–LEN(F1))&F1,

ROW(INDIRECT(“1:16”)),1)*(MOD(ROW(INDIRECT(“1:16”)),2)+1)/

10)+MOD((MID(REPT(“0”,16–EN(F1))&F1,ROW(INDIRECT(“1:16”)),

1)*(MOD(ROW(INDIRECT(“1:16”)),2)+1)),10))),10)=0,”VALID”,

“INVALID”)}

You can delete the now superfluous intermediate formulas. The final megaformula, a mere 229

characters in length, does the work of 51 intermediary formulas!

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