Tough Problem 3 (Formula)

[pgc01]

Hi all

I'm back with one more though problem. As Barry suggested, this is simpler, but still interesting, I hope.


As many of you know, the Luhn algorithm is used in most of the credit and debit cards to validate the number. For ex., with my cards that have 16 digits, given the 15 first digits I could find the 16th.

You have a brief explanation of the algorithm used here (look especially at the example for the number 49927398716):

Luhn algorithm - Wikipedia


Problem:

Given the 15 first digits of a credit/debit card, calculate the 16th.


Rules:

- if possible one formula

- better if the algorithm works also for numbers that don't have 15 digits (like the Wiki example)

- MVPs and experienced users please wait 12 hours before posting to allow others to have some fun.

(- No search the web for a solution, it will spoil the fun)


Remark: you can try the solution with your own cards

I post some solutions. The 4th doesn't have 15 digits.

Book5.xlsm
	A	B
1	123456789012345	2
2	129278234762354	6
3	987654321098765	8
4
5	4992739871	6
Sheet2

 

[pgc01]

Hi again

I thought that instead of just posting the link it would be easier if I would post a brief explanation of the Luhn algorithm.

Let's consider a possible credit card number:

1234567890123452

The Luhn Algorithm is very simple.

Consider the digits ordered form right to left.

Multiply by 2 all the digits in even positions

(5*2) (3*2) (1*2) (9*2) (7*2) (5*2) (3*2) (1*2)

Add all the digits in odd positions with the digits of the double of the numbers in even positions, that we calculated before.

2 + (1+0) + 4 + (6) + 2 + (1) + 0 + (1+8) + 8 + (1+4) + 6 + (1+0) + 4 + (6) + 2 + (1)

The result is 60

Since the result is a multiple of 10, the number is valid.

 

[schielrn]

hmmm... I cannot come to the correct answer for rows 2 and 3, but I match the answers for the wikipedia article and 1 and 4, so I must be missing something because it doesn't even work on my own credit card. I can't wait to see some of the answers because mine is far from elegant.

 

[schielrn]

Ok, I think I took the wikipedia article the wrong way? Aren't you suppose to for row 1 add 1 & 6 to add to 7? and not use 16 in the sum? Maybe I misread something. If that is the case, could the last number also be 3, since that would then sum to 120?

 

[pgc01]

Hi schielrn

I'm glad you looked into this so soon. You are absolutely right. I have looked at this at the beginning of the week and now I messed up. I'll ask a moderator to correct my second post.

As the Wikipedia article says, you are right, you have to add the digits of the double of the numbers in even positions from the right.

My examples are right, just my explanation wasn't.

Can I ask you to confirm the examples in rows 2 and 3?

Book5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Digit		Even *2	Add Digits				Digit		Even *2	Add Digits
2	1292782347623540		1	6	6	6		9876543210987650		1	8	8	8
3			2	4	8	8				2	5	10	1
4			3	5	5	5				3	6	6	6
5			4	3	6	6				4	7	14	5
6			5	2	2	2				5	8	8	8
7			6	6	12	3				6	9	18	9
8			7	7	7	7				7	0	0	0
9			8	4	8	8				8	1	2	2
10			9	3	3	3				9	2	2	2
11			10	2	4	4				10	3	6	6
12			11	8	8	8				11	4	4	4
13			12	7	14	5				12	5	10	1
14			13	2	2	2				13	6	6	6
15			14	9	18	9				14	7	14	5
16			15	2	2	2				15	8	8	8
17			16	1	2	2				16	9	18	9
18
19				Sum		80					Sum		80
Sheet2

 

[Ron Coderre]

Hi schielrn

Can I ask you to confirm the examples in rows 2 and 3?

I'm not schielrn...but, I checked your examples. They're all correct.

 

[pgc01]

Thank you Ron.

I'll be waiting for your formula, but you are in the category of those that can only post in a few hours, not to spoil the fun.

 

[pgc01]

P. S.

I didn't write it in the first post, but since many of us still don't have excel 2007, I'd prefer a formula that works also in previous versions.

 

[yytsunamiyy]

I don't have a formula solution to your problem pgc - great idea by the way - but in an earlier thread of mine I applied myself to checking the validity of CC.

here is the VBA-solution for the Luhn algorythm I came up with, maybe it helps those that have the time to find a solution for this problem

CardValidation:
    
     For i = 1 To Len(wert) '-------read digits in Cardnumber
         CheckNr(i) = VBA.Mid(Me.TextBoxCardNr2.Value, i, 1)
     Next i
     
     For j = Len(wert) - 1 To 1 Step -2 '----double alternate digits starting with right - 1
         CheckNr(j) = CheckNr(j) * 2
         If CheckNr(j) > 8 Then ' if doubled is 2-digit number calculate Sum of digits
             CheckString = CheckNr(j)
             CheckPart(1) = VBA.Left(CheckString, 1)
             CheckPart(2) = VBA.Right(CheckString, 1)
             CheckNr(j) = CheckPart(1) + CheckPart(2)
         End If
     Next j
     
     '------add all CheckNr's to make CheckSum
     For k = 1 To Len(wert)
         CheckSum = CheckSum + CheckNr(k)
     Next k
     
     '------check that Checksum / 10 does not leave remainder - if so CardNr is invalid
     If Not CheckSum Mod 10 = 0 Then
         GoTo ErrorMsgCardInvalid
     Else
        'more stuff
     End If

If anyone wants to see the whole thread - here its is.

 

[pgc01]

Hi yytsunamiyy

Thank you for posting. Although your solution is not considered for this problem, because we want a formula solution, it's very helpful, since it works and helps anyone that is thinking about which algorithm to use.

If you prefer a vba problem I'll be posting one in the next days. Although a classic, it may be fun to revisit.

Cheers