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
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
Table 21-4: Information about Four Major Credit Cards
13 or 16
American Express 34 or 37
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
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.