An International Standard Book Number (“ISBN”) is a unique number given to every edition of a book. The Cambridge International ICT and IT subjects frequently use ISBNs as examples of data in Excel and Access exercises and assessments. This tutorial focuses on creating a check digit for an ISBN.
In this tutorial:
1. ISBN check digit algorithm
Each of the first 12 digits of the ISBN is alternately multiplied by 1 and 3. The check digit is
equal to 10 minus the remainder resulting from dividing the sum of the weighted products
of the first 12 digits by 10 with one exception. If this calculation results in an apparent
check digit of 10, the check digit is 0.
The main function of this calculation is to prevent numbers from being transposed (“swapped around”).
2. Example
Use the following steps to calculate the check digit for the ISBN 978-92-95055-12-?
2.1 Step 1
Determine the sum of the weighted products for the first 12 digits of the ISBN (see
following table).
| GS1 element | Registration group element | Registrant element | Publication element | Check digit | Sum | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ISBN | 9 | 7 | 8 | 9 | 2 | 9 | 5 | 0 | 5 | 5 | 1 | 2 | ? | |
| Weight | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | 1 | 3 | — | |
| Product | 9 | 21 | 8 | 27 | 2 | 27 | 5 | 0 | 5 | 15 | 1 | 6 | — | 126 |
2.2 Step 2
Divide the sum of the weighted products of the first 12 digits of the ISBN calculated in Step 1 by 10, determining the remainder:
126 / 10 = 12 remainder = 6
2.3 Step 3
Subtract the remainder calculated in Step 2 from 10. The resulting difference is the value of the check digit with one exception. If the remainder from step 2 is 0, the check digit is 0.
10 – 6 = 4
Check digit = 4
ISBN = 978-92-95055-12-4
The following mathematical formula is an alternative way of expressing the calculation of the check digit:
Check digit = mod 10 (10 – [mod 10 {sum of weighted products of the first 12 ISBN digits}])
Check digit = mod 10 (10 – [mod 10 {126}])
Check digit = 4
The sum of the weighted products of the first 12 digits plus the check digit must be divisible by 10 without a remainder for the ISBN to be valid.
3. Next steps
Make the theory practical: complete the Using Excel’s MOD() function tutorial.
References:
- International ISBN Agency. (2017). Available at: https://www.isbn-international.org/content/isbn-users-manual/29 7th Ed. p. 33 London.
- ISBN (2024) Wikipedia. Wikimedia Foundation. Available at: https://en.wikipedia.org/wiki/ISBN#ISBN-13_check_digit_calculation (Accessed: 27 May 2024).