Luhn Algarithm for Library Barcoding

**Sethinstein** · 05-22-2012, 10:42 AM

Hello my friends. I have developed an equation calculating the Check digit for a set of library barcodes. There are some faulty Barcodes that we are searching for because they are interfering with a digitization process. I have a three equation test for determining the usability of our barcodes however I want to nest two if statements into it in order to make it a singe equation line that says good or bad. listed below are my equations

1 Check fig. (before subracting from 10)

=MOD(SUMPRODUCT((MID(A2,ROW(INDIRECT("1:13")),1)>"0 ")*(1+MOD(MID(A2,ROW(INDIRECT("1:13")),1)*(1+MOD(ROW(INDIRECT("1:13")),2))-1,10-MOD(ROW(INDIRECT("1:13")),2)))),10)

2. if greater than 0 subract from 10 (elimintes numbers ending in 0 from skewing results)

=IF(B2>0,10-B2,0,)

3. If checksum digit is equal to barcodes checksum the Barcode is "good"

=(IF(C2-(RIGHT(A2,LEN(A2)-13))=0,"Good","BAD"))

Current simulation:

31924021258300 0 0 Good
31924024495966 4 6 Good
31924028906332 9 1 BAD
31924029885823 7 3 Good
31924029908351 9 1 Good

I would like to see the IF statements Nested so that one equation will give me an accurate "GOOD" or "BAD" response.

thanks in advance.

-S

**daddylonglegs** · 05-22-2012, 11:11 AM

This formula will give you the correct check digit for a 13 digit number (also accommodates numbers ending in zero)

=MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:13")),1)*(MOD(ROW(INDIRECT("1:13"))+14,2)+1),"00"),{1,2},1)),10)

so you can amend that to give you good or bad, depending on whether the check digit matches the last digit in A2

=IF(MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:13")),1)*(MOD(ROW(INDIRECT("1:13"))+14,2)+1),"00"),{1,2},1)),10)= RIGHT(A2)+0,"Good","Bad")

You might want to also include a check to ensure that there are 14 digits because the above doesn't explicitly do that, i.e.

=IF((MOD(SUMPRODUCT(-MID(TEXT(MID(A2,ROW(INDIRECT("1:13")),1)*(MOD(ROW(INDIRECT("1:13"))+14,2)+1),"00"),{1,2},1)),10)= RIGHT(A2)+0)*(LEN(A2)=14),"Good","Bad")

**Sethinstein** · 05-22-2012, 11:18 AM

Sorry Legs, No Dice on your formulas. Not sure exactly where it is wrong but it is.

**abousetta** · 05-22-2012, 11:19 AM

Maybe try this bad boy:

Please Login or Register  to view this content.

**daddylonglegs** · 05-22-2012, 11:25 AM

I think my versions should work OK, they didn't post too well initially, I needed to reformat - see attachment where I show all 3 for demo purposes but I suggest you use just the one in column D

I added a 15 digit number at the end so that version demonstrates how that would be deemed to be "bad"