Tough Problem 3 (Formula)

[Peter_SSs]

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
	A	B	C
1	123456789012345	2
2	129278234762354	6
3	987654321098765	8
4
5	4992739871	6
6
Tough Problem 3

 

[yytsunamiyy]

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

 

[fairwinds]

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)))

 

[Peter_SSs]

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.

 

[fairwinds]

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))))

 

[barry houdini]

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)

 

[pgc01]

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

 

[barry houdini]

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)

 

[pgc01]

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

 

[schielrn]

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?