Tough Problem 3 (Formula)

pgc01

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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:
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.
 
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?
 
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:
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. :)
 
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.
 
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.
 
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
 

Forum statistics

Threads
1,212,927
Messages
6,110,731
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top