Luhn check digit formula

[bhmcpfs]

I'm charged with coming up with a formula using the "Luhn check digit formula" for a 10-digit number, calculated as follows: (The check digit is the last number.)

1. Double the value of alternate digits beginning with the first digit.
2. Add 24 to the individual digits of the products resulting from step 1 to the unaffected digits from the original number.
3. Subtract the total obtained in step 2 from the next higher number ending in zero. This is the check digit. If the total obtained in step 2 is a number ending in zero, the check digit is zero.

Example: Number is 1234567893

1 2 3 4 5 6 7 8 9 3
2 6 10 14 18 (step 1)
24+2+2+6+4+1+0+6+1+4+8+1+8=67 (step 2)
70-67=3 (step 3)

I'm not getting anywhere with my attempts, and the formula is getting uglyier (is that a word?) with each attempt. Any help from the board would be much appreciated! Thanks in advance, and sorry for the long post...

 

[Richard Schollar]

I'm not sure I understand - could you explain where the constant of 24 comes into it?

As it stands the formula I provided does calculate the Luhn check number but only for card numbers with even numbers of digits. I think it will be possible to produce an alternative that will work for odd total digits too, but let me first understand your requirements.

 

[bhmcpfs]

24 is a constant added as I have a leading identifying number to work with as part of the id number I have to verify. (I stripped it out to simplfy my work.) In my original post I stated this was part of 'step 2'. My number to verify is really an odd number (the first 9-digits) because the 10th digit is acutally my check digit. I'd think your formula could be easily modified to ignore the last digit, or written to only consider 9 digits? Hope this helps. Sorry if I'm not explaining it well.

 

[Richard Schollar]

Maybe like this:

Excel Workbook
	A	B	C
1	Card Number	Luhn Number	Card Length
2	1234567897	50	10
3	110480650	22	9
4	135631018	27	9
5
6	Full num	Modified Luhn (Exclude Chk Digit)	Card Length
7	1104806504	22	10
8	1356310189	27	10
Sheet1

 

[bhmcpfs]

Formula in b7, b8 seems to be working in small batch of test data! On to adding in the constant, rounding up the result, subtracting, comparing with the check digit... Many thanks!

 

[zhosen]

Maybe like this:

Sheet1

*	A	B	C
Card Number	Luhn Number	Card Length
*	*	*
Full num	Modified Luhn (Exclude Chk Digit)	Card Length

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:94px;"><col style="width:85px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1234567897[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]110480650[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]135631018[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]1104806504[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]1356310189[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 {=SUM(--MID(TEXT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2))),2)+1),"00"),{1,2},1))} B3 {=SUM(--MID(TEXT(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A3))),2)+1),"00"),{1,2},1))} B4 {=SUM(--MID(TEXT(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A4))),2)+1),"00"),{1,2},1))} B7 {=SUM(--MID(TEXT(MID(A7,ROW(INDIRECT("1:"&LEN(A7)-1)),1)*(MOD(ROW(INDIRECT("1:"&LEN(A7)-1)),2)+1),"00"),{1,2},1))} B8 {=SUM(--MID(TEXT(MID(A8,ROW(INDIRECT("1:"&LEN(A8)-1)),1)*(MOD(ROW(INDIRECT("1:"&LEN(A8)-1)),2)+1),"00"),{1,2},1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi,
I came here from a different post. My problem is similar to this but my requirements are as bellow-
1. I have 14 digit of data
2. I need the 15th digit as check digit by Luhn formula
3. Here i am adding some example:
35704905000001 "3" here 3 is the check digit; without quote
35704905000002 "1"
35704905000003 "9" etc.
I want to put it on excel sheet in A1 and i want to get the total 15 digit in B1. Please help me to find the formula. Thanks for any help in this regards. Have great day!

 

[zhosen]

Hi,
I came here from a different post. My problem is similar to this but my requirements are as bellow-
1. I have 14 digit of data
2. I need the 15th digit as check digit by Luhn formula
3. Here i am adding some example:
35704905000001 "3" here 3 is the check digit; without quote
35704905000002 "1"
35704905000003 "9" etc.
I want to put it on excel sheet in A1 and i want to get the total 15 digit in B1. Please help me to find the formula. Thanks for any help in this regards. Have great day!

Finally i found the solution that was very near for my expected solution and i just change few things and its working perfectly now. I did the bellow things-
1. in column A2 "1234567890123" and
2. in column B2 " =MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A2)))+1,2)+1),"00"),{1,2},1)),10)"
3. in column B2 you will get the 14 digit check sum result
4. and finally to combined A2 and B2 i put on C2 "=A2&B2"
Thats it!!