Formula Challenge 023 - IMEI Luhn Check

[MicahDail]

Here's my contribution. Below is an actual implementation of the Luhn algorithm (according to Wikipedia and confirmed by http://www.ee.unb.ca/cgi-bin/tervo/luhn.pl). Whether a digit is multiplied by 2 actually depends on the relationship between that digit and the end of the integer, . Also, it allows a check digit value of 0.

This utilizes ROW(INDIRECT()) to account for integers of varying length. Props to Lori for her brilliant use of TEXT().

{=MOD(SUM(--TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*IF(ISODD(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))),2,1),"0 0\/1")),10)=0}

 

[DBExcel]

Solved in Excel PowerQuery Add-in - Longer but much easier to follow especially with the help of the Query Editor.

My solution also gives the Luhn digit if given an input of 14 digits and throws an easy to comprehend error message if given an invalid input. See demo video here

Full query function text:

(Input as any) =>

let

TextControl = try Number.ToText(Input) otherwise Input,
ListChars = Text.ToList(TextControl),
CheckFigs = List.FirstN(ListChars, 14),
ControlChar = if List.Count(ListChars) = 15 then ListChars{14} else null,
EveryOther = List.Alternate(CheckFigs, 1, 1, 0),
NonDoubled = List.Difference(CheckFigs, EveryOther),
DoubledEveryOther = List.Transform(EveryOther, (_) => Number.ToText(List.Product({Number.From (_), 2}))),
SplitoutDigits = List.Transform(DoubledEveryOther, (_) => Text.ToList(_)),
CombineSubDigits = List.Combine(SplitoutDigits),
CombineCheckFigs = List.Transform(List.Combine({CombineSubDigits, NonDoubled}), (_) => Number.From(_)),
SumAllDigits = List.Sum(CombineCheckFigs),
IMEIResult = 10 - Number.Mod(SumAllDigits, 10),

Output = if List.Count(ListChars) = 14
then IMEIResult
else if (List.Count(ListChars) <> 15)
then Error.Record("Required Numeric Input of Length 14 or 15.", "Error in Input")
else IMEIResult = Number.From(ControlChar)

in
Output

 

[Malcolm Russell]

Hi DBExcel,

The Powerquery solution was awesome and saved me heaps of time.
I had to convert it to 9 digit validation.
I did find 2 bugs however and thought I would share,
NonDoubled = List.Difference(CheckFigs, EveryOther) would occasionally product the wrong output. I changed it to,
NonDoubled = List.Alternate(CheckFigs,1,1,1), instead.
Also LuhnResult can return a value of 10 instead of 0 so inputs with a controlchar of 0 would fail.

My function converted from yours to validate 9 digits,


(Input as any) =>
let
ToText = try Number.ToText(Input) otherwise Input,
ListChars = Text.ToList(ToText),
CheckFigs = List.FirstN(ListChars,8),
ControlChar = if List.Count(ListChars) = 9 then ListChars{8} else null,
EveryOther = List.Alternate(CheckFigs,1,1,0),
NonDoubled = List.Alternate(CheckFigs,1,1,1),
DoubleEveryOther = List.Transform(EveryOther,(_) => Number.ToText(List.Product({Number.From (_),2}))),
SplitOutDigits = List.Transform(DoubleEveryOther, (_) => Text.ToList(_)),
CombineSubDigits = List.Combine(SplitOutDigits),
CombineCheckFigs = List.Transform(List.Combine({CombineSubDigits,NonDoubled}),(_) => Number.From(_)),
SumAllDigits = List.Sum(CombineCheckFigs),
Mod10 = Number.Mod(SumAllDigits,10),
LuhnResult = if Mod10 = 0 then 0 else 10 - Mod10,
Output = if (List.Count(ListChars) <> 9) then false
else LuhnResult = Number.From(ControlChar)
in
Output


Thanks
Malcolm.

 

[issam]

could you please send the excel file
to issam_comm @ yahoo.com

 

[Richard Macbeth]

Here is a formula, based on Somendra's but with additional logic to account for a checksum of zero. It returns TRUE or FALSE unless the number of characters is wrong in which case it is #VALUE!. Note the IMEI is in column J (and SIM in K)
=IF((RIGHT(J2,1)+0=0),10, RIGHT(J2,1)+0 )=10-MOD(SUMPRODUCT(INT(2*(MID(J2,2*ROW($1:$7),1)+0)/10)+MOD(2*(MID(J2,2*ROW($1:$7),1)+0),10)+(MID(J2,(2*ROW($1:$7))-1,1)+0)),10)

And as they are related, modifying for SIM number (19 digits)
=IF((RIGHT(K2,1)+0=0),10, RIGHT(K2,1)+0 )=10-MOD(SUMPRODUCT(INT(2*(MID(K2,2*ROW($1:$9),1)+0)/10)+MOD(2*(MID(K2,2*ROW($1:$9),1)+0),10)+(MID(K2,(2*ROW($1:$9))-1,1)+0)),10)

 

[Peter Bartholomew]

Why doesn't it surprise me that Lorimer managed to come up with amazing solutions!
Now, 10 years on and with an entirely new calculation engine, one could have

= MAP(listIMEI, Checkdigitλ)

where

Checkdigitλ
= LET(
    digits,      EXPLODEλ(IMEI),
    LuhnDigit,   VALUE(TAKE(digits, -1)),
    pairs,       WRAPROWS(DROP(digits, -1), 2),
    weighted,    {1, 2} * pairs,
    tens,        QUOTIENT(weighted, 10),
    checkdigit,  10 - MOD(SUM(weighted, tens), 10),
    checkdigit = LuhnDigit
  )

Note: Unlike the traditional challenge there is no attempt to go for brevity, I prize clarity more highly.

 

[p45cal]

Another:

=LET(d,A2,MOD(10-MOD(REDUCE(0,SEQUENCE(14),LAMBDA(a,b,a+LET(c,VALUE(MID(d,b,1)),IF(ISODD(b),c,QUOTIENT(c*2,10)+MOD(c*2,10))))),10),10)=VALUE(MID(d,15,1)))

where A2 contains the IMEI number. It returns TRUE if the 15th digit is correct.