Tough Problem 3 (Formula)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
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:

Some videos you may like

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
Joined
Apr 25, 2006
Messages
19,778
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
Joined
Apr 4, 2007
Messages
6,934
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
Joined
Apr 4, 2007
Messages
6,934
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
Joined
Apr 25, 2006
Messages
19,778
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
1DigitEven *2Add DigitsDigitEven *2Add Digits
21292782347623540166698765432109876501888
3248825101
435553666
5436647145
652225888
76612369189
877777000
984888122
1093339222
111024410366
121188811444
13127145125101
141322213666
15149189147145
161522215888
1716122169189
18
19Sum80Sum80
Sheet2
 
Last edited by a moderator:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
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
Joined
Apr 25, 2006
Messages
19,778
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
Joined
Mar 17, 2008
Messages
963
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
Joined
Apr 25, 2006
Messages
19,778
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,351
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top