Microsoft Office Tutorials and References

In Depth Information

You must insert parentheses around the formula text copied from cell B1. Without the

parentheses, the formula does not evaluate correctly.

Using a megaformula to determine the validity of a credit

card number

Many people are not aware that you can determine the validity of a credit card number by using a relatively

complex algorithm to analyze the digits of the number. In addition, you can determine the type of credit card by

examining the initial digits and the length of the number. Table 21-4 shows information about four major credit

cards.

Table 21-4: Information about Four Major Credit Cards

Credit Card

Prefix Digits

Total Digits

MasterCard

51–55

16

Visa

4

13 or 16

American Express 34 or 37

15

Discover

6011

16

Validity, as used here, means whether the credit card number itself is a valid number as

determined by the following steps. This technique, of course, cannot determine wheth-

er the number represents an actual credit card account.

You can test the validity of a credit card account number by processing its checksum. All account numbers used

in major credit cards use a Mod 10 check-digit algorithm. The general process is as follows:

1.
Add leading zeros to the account number to make the total number of digits equal 16.

2.
Beginning with the first digit, double the value of alternate digits of the account number. If the result is a

two-digit number, add the two digits together.

3.
Add the eight values generated in Step 2 to the sum of the skipped digits of the original number.

4.
If the sum obtained in Step 3 is evenly divisible by 10, the number is a valid credit card number.

The example in this section describes a megaformula that determines whether a credit card number is a valid

number.

The basic formulas

Figure 21-6 shows a worksheet set up to analyze a credit card number and determine its validity. This workbook

uses quite a few formulas to make the determination.