Microsoft Office Tutorials and References

In Depth Information

When entering a credit card number that contains more than 15 digits, you must be

careful that Excel does not round the number to 15 digits. You can precede the number

with an apostrophe or preformat the cell as Text (using Home
⇒
Number
⇒
Number

Format
⇒
Text).

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 fol-

lows:

=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 re-

turned 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 re-

mainder 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 these calculations will be an array formula because the intermediary formu-

las 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.