Introduction

As part of something I was working on yesterday I had to look at check digits in an ISBN. A check digit is a number at the end of, eg, a book code number, its ISBN, that confirms the number you have ust typed in is correct. For example, ISBN9781861972712 is the correct code because the number 2 at the end is the correct check digit.

What I then did as an exercise was to create a simple spreadsheet to find the check digit according to the rules:

  • multiply each of the 12 digits in the ISBN by given numbers, 1,3,1,3,1,3,1,3,1,3,1,3 and add together the results.
  • take modulo 10 of the results and if the answer is 0, your check digit is 0
  • if modulo 10 is not 0 then your check digit is 10 – the modulo
  • in this case the sum of the multiplications is 118 so modulo 10 in this case is 118/10=11.8 ==> 8

MODULO

To find the modulo in excel =MOD(D16,10) where the sum is in D16 and the modulo divisor is 10

Several interesting things happened to my spreadsheet!

  • I copied and pasted the ISBN to a single cell, I1, in my spreadsheet
  • I used =LEFT(I1,1) to extract the first digit in the ISBN to put it in cell D4
  • I used =MID(I$1,A5,1)*B5 to extract the second digit, 7, in the ISBN to put it in cell D5
  • I then copied that MID() formula down to D14 so that I had all 12 digits extracted … the 13th digit is the check digit
  • I had then set up my table like this:

check_digit

Initially, I created column D to do the calculations on which to based my check digit proof and that was fine: open the file and check my formulas

I then thought all was well until I tried to use SUMPRODUCT() to give me all of the work in column D in ust one cell: =SUMPRODUCT(B4:C15) but it would not work.I can freely admit that I made a mistake with my SUMPRODUCT() formula and I have left the error in the spreadsheet for you to find. However, that mistake led me to explaining the other ways of finding the answer! That’s serendipity!

I learned quickly that the LEFT() and MID() functions were giving me text not values in column C so I tried two things:

  • =LEFT(I1,1)*1 and =MID(I$1,A5,1)*1
  • =INT(LEFT(I1,1)) and =INT(MID(I$1,A5,1))

Both seemed to give the values now but still SUMPRODUCT() will not work fully

I got tired of that and used more alternatives:

  • in C16 I entered {=SUM(B4:B15*C4:C15)} which is ARRAY entered and it works
  • in E16 I entered {=SUM({1,3,1,3,1,3,1,3,1,3,1,3}*{9,7,8,1,8,6,1,9,7,2,7,1})} which is also ARRAY entered and that works
  • Of course =SUM(D4:D15) in D16 is working fine!

So What’s Going on?

You can see the rest of the table works, cells D17 to D19: divisor for modulo, modulo result and check digit … so his has now turned into a detective story!

In the range F4:F15 you see the word TRUE: it says true because C4:C15 contain the INT() entered versions of LEFT() and MID() … if you leave these formulas as, eg, =LEFT(I1,1) in C$ and =MID(I$1,A5,1) in C% then F4 and F5 become FALSE. That is, they confirm they are giving us text not values.

Some progress!

Conclusion and one more thing to try

I am sure I know the problem now and as I type this I am about to test my hypothesis: that the ISBN I copied and pasted is the problem. I am not going to go to cell I1 and overtype that number to see what happens!!

And the answer is … rubbish!

I overtyped the ISBN and nothing changed. I copied and pasted special by multiplying the ISBN by 1, I right clicked I1 and Format Cell changed the format manually, first to general and then to … nothing worked.

So, you have seen several strategies that I used to get this to work and it has worked. However, we are left with three problems:

  • LEFT() and MID() don’t return values but text
  • the input cell seems to be misbehaving but I cannot work out why
  • Since I created this page I realised the mistake I made realise that it is no longer a problem.

Download the file and see what you can come up with: this is your chance to find my error and correct it! Click on this link … ISBN_check_digit

 

Duncan Williamson

%d bloggers like this: