Microsoft Office Tutorials and References
In Depth Information
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–LEN(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!
Figure 21-7 shows this formula at work.
Search JabSto ::




Custom Search