Microsoft Office Tutorials and References

In Depth Information

**Megaformula Examples**

Column A contains a series of integers from 1 to 16, each representing the digit positions of the

credit card.

Column B contains formulas that extract each digit from cell F2. For example, the formula in cell

B5 is as follows:

=MID($F$2,A5,1)

Column C contains the multipliers for each digit: alternating 2s and 1s.

Column D contains formulas that multiply the digit in column B by the multiplier in column C. For

example, the formula in cell D5 is

=B5*C5

Column E contains formulas that sum the digits displayed in column D. A single digit value in

column D is returned directly. For two-digit values, the sum of the digits is displayed in Column E.

For example, if column D displays 12, the formula in column E returns 3: that is, 1 + 2. The formula

that accomplishes this is as follows:

=INT((D5/10)+MOD((D5),10))

Cell E21 contains a simple SUM formula to add the values in column E:

=SUM(E5:E20)

The formula in cell G1, which follows, calculates the remainder when cell E21 is divided by 10. If

the remainder is 0, the card number is valid, and the formula displays
VALID.
Otherwise, the

formula displays
INVALID.

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

Convert to array formulas

The megaformula that performs all of these calculations will be an array formula because the

intermediary formulas occupy multiple rows.

First, you need to convert all the formulas to array formulas. Note that columns A and C consist of

values, not formulas. To use the values in a megaformula, they must be generated by formulas —

more specifically, array formulas.