Microsoft Office Tutorials and References

In Depth Information

Enter the following array formula into the range A5:A20. This array formula returns a series of 16 consecutive

integers:

{=ROW(INDIRECT(“1:16”))}

1.
For column B, select B5:B20 and enter the following array formula, which extracts the digits from the cred-

it card number:

{=MID($F$2,A5:A20,1)}

2.
Column C requires an array formula that generates alternating values of 2 and 1.

Such a formula, entered into the range C5:C20, is shown here:

{=(MOD(ROW(INDIRECT(“1:16”)),2)+1)}

3.
For column D, select D5:D20 and enter the following array formula:

{=B5:B20*C5:C20}

4.
Select E5:E20 and enter this array formula:

{=INT((D5:D20/10)+MOD((D5:D20),10))}

Now the worksheet contains five columns of 16 rows, but only five actual formulas (which are multicell array

formulas).

Build the megaformula

To create the megaformula for this task, start with cell G1, which is the cell that has the final result. The original

formula in G1 is

=IF(MOD(E21,10)=0,”VALID”,”INVALID”)

1.
Replace the reference to cell E21 with the formula in E21.

Doing so results in the following formula in cell G1:

=IF(MOD(SUM(E5:E20),10)=0,”VALID”,”INVALID”)

2.
Replace the reference to E5:E20 with the array formula contained in that range. Now the formula becomes

an array formula, so you must enter it by pressing Ctrl+Shift+Enter.

After the replacement, the formula in G1 is as follows:

{=IF(MOD(SUM(INT((D5:D20/10)+MOD((D5:D20),10))),10)=0,

“VALID”,”INVALID”)}

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.