Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

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 credit 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”)}