Tough Problem 3 (Formula)

The previous problems have seemed too difficult for me, but I think I can have a go at this one and the requisite 12 hours has passed with no takers so I'll kick this one off.

Not sure if this works in all cases but seems to for the samples given and for my own card.

Excel Workbook
ABC
11234567890123452
21292782347623546
39876543210987658
4
549927398716
6
Tough Problem 3
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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

I am the first to admit that formulas are not my forte :)
I wasn't trying to post a solution to the problem, I was just thinking that a working Luhn algorythm might help explain the concept further (or maybe just confuse people and send them down the wrong track...). I'm looking forward to seeing the formula solutions.

regards,

Stephan
 
Hi,

=10-right(sumproduct(--mid(text(mid(text(a1,rept(0,15)),row(indirect("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)))
 
Hi,

=10-right(sumproduct(--mid(text(mid(text(a1,rept(0,15)),row(indirect("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)))
G'day fairwinds

Much shorter than mine and still will be when you do what I think is a required final tweak. For ...
123456789012346
... you formula returns 10, when I believe it should be 0. It's a similar problem I had to deal with when finalising my attempt. :)
 
Right. Let's wrap it in Right() ;) maybe with also a coercer if needed.

=RIGHT(10-RIGHT(SUMPRODUCT(--MID(TEXT(MID(TEXT(A1,REPT(0,15)),ROW(INDIRECT("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1))))
 
That's certainly better than anything I came up with, fairwinds......but perhaps use MOD instead of two RIGHTs, i.e.

=MOD(SUMPRODUCT(-MID(TEXT(MID(TEXT(A1,REPT(0,15)),ROW(INDIRECT("1:15")),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10)
 
Hi

Peter

Welcome. I tested your formula with 15 digit codes and it works ok.

Fairwinds, Barry

Both formulas before and after Barry's modifications are ok for 15 digit codes.


I'll be waiting for the formulas that work independently from the number of digits.

- better if the algorithm works also for numbers that don't have 15 digits

As I wrote in my first post, the formula should preferably be independent from the number of digits.

I honestly don't know how many digits we should consider. I already knew that some codes with 20 digits used Luhn's algorithm. Today I came accross some codes in the European Banking system that use 23 digits.

Since I don't know what's the maximum number of digits that makes sense, let's be reasonable and consider, for ex., a maximum of 99 digits.

Cheers
 
Hello pgc,

My first attempt, before I saw Peter's and fairwinds' suggestions, was this formula which should deal with any number of digits from 1 to 99 (and more)

=MOD(LEN(SUBSTITUTE(A1,0,""))-LEN(A1)-SUMPRODUCT((MOD(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1)-1,9)+1)),10)

...or an amalgam of fairwinds' version and the above.....

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)
 
Last edited:
Hi all

I think we can all agree that we have solutions of the best quality in this thread. I consider the last formula posted by Barry, the "an amalgam of Fairwinds' version and the above" formula really great.

I had posted an answer to this problem about 2 years ago, here:

http://www.mrexcel.com/forum/showthread.php?p=1190595

One thing interesting in that thread is that after the formula for a code with digits, we continue with an extension for alphanumeric codes. That was my first option for this problem, before I opted for digits only.

Any formula posted here was much better than mine, that was longer and array entered. This is one good point of these threads, you end up learning new and better approaches to the problems.

I'm really happy with the results so far, although I'd like to have more participation, which I hope we'll have in the next problems.


I'll try to post the next Tough Problem (VBA) in the next couple of hours.


I'm very happy to annouce that the next Tough Problem (Formula) will be posted by Barry in the next couple of days. This means that I can also participate! :)

Cheers
 
Last edited:
This is what I have, but when I was evaluating it seemed to be handling all the numbers correctly, but haven't been able to get back to it since Friday. And also it gives me #Value error in pre-2007, so I can't work on it from home:

=10-MOD(SUMPRODUCT(--(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),--(ISEVEN(ROW(INDIRECT("1:"&LEN(A2))))))*2-SUMPRODUCT(--((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*(ISEVEN(ROW(INDIRECT("1:"&LEN(A2))))*2)>9))*9+SUMPRODUCT(--(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),--(ISODD(ROW(INDIRECT("1:"&LEN(A2)))))),10)

Let me know of any pointers or help you can give me for this?
 

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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