# Tough Problem 3 (Formula)

#### pgc01

##### MrExcel MVP
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
AB
11234567890123452
21292782347623546
39876543210987658
4
549927398716
Sheet2

Last edited by a moderator:

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### pgc01

##### MrExcel MVP
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.

Last edited by a moderator:

#### schielrn

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
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
ABCDEFGHIJKLM
21292782347623540166698765432109876501888
3248825101
435553666
5436647145
652225888
76612369189
877777000
984888122
1093339222
111024410366
121188811444
13127145125101
141322213666
15149189147145
161522215888
1716122169189
18
19Sum80Sum80
Sheet2

Last edited by a moderator:

#### Ron Coderre

##### MrExcel MVP
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### Well-known Member
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 Code:
``````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

##### MrExcel MVP
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