During the period 23/12 to 6/1, we will have slightly longer response times than usual. Merry Christmas and a Happy New Year! 🎄✨
Back to blog

Barcode challenge

Barcode challenge

Posted on by Emil
Download Excel-fileDownload

In this week’s challenge, you need to help your local post office whose package sorting system is broken. The packages have a barcode that, when scanned, are converted into a series of ones and zeroes (bits).

The destination country is written in the barcode. It is determined by the two first bits and by the last bit. Write a formula in column U that outputs the destination of the package.

Italy11xxxxx1
France11xxxxx0
Germany10xxxxx1
Spain01xxxxx0
Russia00xxxxx0
USA01xxxxx1



Drop your solution in the comments below! 🙂

Our solution (solves both medium and hard version):


=IF(AND(AND(M6;N6);T6);”Italy”;IF(AND(AND(M6;N6);XOR(N6;T6));”France”;IF(AND(AND(M6;T6);OR(T6;N6));”Germany”;
IF(AND(XOR(M6;N6);XOR(N6;T6));”Spain”;IF(AND(AND(NOT(M6);NOT(N6);NOT(T6)));”Russia”;
IF(AND(XOR(M6;N6;);AND(N6;T6));”USA”;”Country not found”))))))

Since the barcodes are represented by bits, we have chosen to solve this challenge with boolean logic. Inside each IF-function we evaluate the specific countries only with logical operators (AND, OR, XOR & NOT).

Read more about boolean logic:

https://www.i-programmer.info/babbages-bag/235-logic-logic-everything-is-logic.html




4 thoughts on “Barcode challenge”

  1. Expanded the Destination table with columns for pos 1, 2, 8. Called it Tdest and used INDEX(MATCH)
    =INDEX(Tdest[Destination];XMATCHNING(1;(N(M6)=Tdest[Pos 1])*(N(N6)=Tdest[Pos 2])*(N(T6)=Tdest[Pos 8])))
    Not figured out how to do it without helper columns. Swedish Excel version.

  2. In this case I prefer a solution to pick the right country from a table. But if I only allowed to use one column I made as follows:

    =IF(ABS(M6&N6&T6)=111;”Italy”;IF(ABS(M6&N6&T6)=110;”France”;IF(ABS(M6&N6&T6)=101;”Germany”;
    IF(ABS(M6&N6&T6)=10;”Spain”;IF(ABS(M6&N6&T6)=0;”Russia”;
    IF(ABS(M6&N6&T6)=11;”USA”;””))))))

  3. Inte speciellt originellt eller elegant men:

    =OM(SAMMAN(M6;N6;T6)=”SANTSANTSANT”;”Italien”;OM(SAMMAN(M6;N6;T6)=”SANTSANTFALSKT”;
    “Frankrike”;OM(SAMMAN(M6;N6;T6)=”SANTFALSKTSANT”;”Tyskland”;OM(SAMMAN(M6;N6;T6)=
    “FALSKTSANTFALSKT”;”Spanien”;OM(SAMMAN(M6;N6;T6)=”FALSKTFALSKTFALSKT”;
    “Ryssland”;OM(SAMMAN(M6;N6;T6)=”FALSKTSANTSANT”;”USA”;””))))))

  4. =SWITCH(CONCATENATE(M6;N6;T6);”111″;”Italy”;”110″;”France”;”101″;”Germany”;”010″;”Spain”;”000″;”Russia”;”011″;”USA”)

    Kul med dessa pussel. Visste inte att Excel hade en switch statement innan jag fick klura på det. /Jeff

Leave a Reply

You must be logged in to post a comment.

More blog posts

Tired of Googling?
Get the best Excel tips every week. Subscribe to our newsletter.

About the author

Emil har jobbat på Learnesy som produktutvecklare i flera år och ligger bakom flera av Learnesys kurser. Han har en utbildning inom datavetenskap med ett stort intresse för det analytiska och teoretiska. Utöver Finska och Svenska snackar Emil även flytande Excelfunktioner!