Hello Mrjames,
Here are 2 macros that should help you out. One will verify if a checksum sequence is correct, and the other will generate a checksum digit for a sequence, and if the sequence is valid, no checksum digit needed, it will return a -1.
Using the number you provided we will get the following result:
Cell A1 formula: =IsLuhnChecksumOK("49015420323751")
Returns TRUE. This sum of the digits equals 50, not 52.
How the Luhn algorithm functions:
1) Starting with the second to last digit, multiply every other digit by 2.
2) If the product is greater than 10 then subtract 9 from the product. This produces the same result as adding the digits of number greater than 9. Sum these values.
3) Sum all the unmultiplied digits and add the sum from step 2. The result is valid if it is an even mutliple of 10.
4 9 0 1 5 4 2 0 3 2 3 7 5 1
Step 1 - double the digits
5(2) 3(2) 3(2) 2(2) 5(2) 0(2) 4(2)
Step 2 - sum and adjust the doubled digits
(10 - 9) + 6 + 6 + 4 + (10 - 9) + 0 + 8 = 26
Step 3 - sum the digits not doubled and the doubled digits. Test is total is evenly divided by 10
1 + 7 + 2 + 0 + 4 + 1 + 9 = 24
24 + 26 = 50
10 Mod 50 = 0
If you have any further questions, you can emial me at [email protected].
Sincerely,
Leith Ross
Bookmarks