Produce scheme-specific calculation formulas.
Initialising algorithm engine for Spreadsheet Formula Generator...
How Spreadsheet Formula Generator Works?
The Spreadsheet Formula Generator writes a single-cell formula for Excel or Google Sheets. MID splits the payload into digits. SUMPRODUCT applies the scheme's weights, and MOD produces the final remainder.
- 1Select the scheme and the payload length.
- 2Choose the cell that holds the payload (A2 by default).
- 3Generate the formula with the weight array for that length.
- 4Format the payload column as text to keep leading zeros.
- 5Copy the formula down the column.
Spreadsheet Formula Generator Formula
The generated formula pattern is =MOD(10 − MOD(SUMPRODUCT(--MID(A2, ROW($1:$n), 1), {weights}), 10), 10) for modulus 10 schemes.
digits = --MID(A2, ROW($1:$n), 1)
weights = {w₁; w₂; …; wₙ}
check = MOD(10 − MOD(SUMPRODUCT(digits, weights), 10), 10)ROW($1:$n)creates the positions 1…n.--turns text digits into numbers.
=MOD(10-MOD(SUMPRODUCT(--MID(A2,ROW($1:$12),1),{1;3;1;3;1;3;1;3;1;3;1;3}),10),10)
A2 = 590123412345 → 7Spreadsheet Formula Generator Conversion Table
The spreadsheet formula table lists 9 tested formulas for cell A2; each one was checked in LibreOffice Calc against the sample payload.
| Scheme | Formula (payload in A2) | Sample A2 | Result |
|---|---|---|---|
| EAN-13 (12 digits) | =MOD(10-MOD(SUMPRODUCT(--MID(A2,ROW($1:$12),1),{1;3;1;3;1;3;1;3;1;3;1;3}),10),10) | 590123412345 | 7 |
| UPC-A (11 digits) | =MOD(10-MOD(SUMPRODUCT(--MID(A2,ROW($1:$11),1),{3;1;3;1;3;1;3;1;3;1;3}),10),10) | 03600029145 | 2 |
| SSCC (17 digits) | =MOD(10-MOD(SUMPRODUCT(--MID(A2,ROW($1:$17),1),{3;1;3;1;3;1;3;1;3;1;3;1;3;1;3;1;3}),10),10) | 00012345678901234 | 3 |
| Luhn (15 digits) | =MOD(10-MOD(SUMPRODUCT(--MID(TEXT(MID(A2,ROW($1:$15),1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10),10) | 411111111111111 | 1 |
| ISBN-10 (9 digits) | =MID("0123456789X",MOD(11-MOD(SUMPRODUCT(--MID(A2,ROW($1:$9),1),{10;9;8;7;6;5;4;3;2}),11),11)+1,1) | 080442957 | X |
| ISSN (7 digits) | =MID("0123456789X",MOD(11-MOD(SUMPRODUCT(--MID(A2,ROW($1:$7),1),{8;7;6;5;4;3;2}),11),11)+1,1) | 0378595 | 5 |
| ABA (8 digits) | =MOD(10-MOD(SUMPRODUCT(--MID(A2,ROW($1:$8),1),{3;7;1;3;7;1;3;7}),10),10) | 02100002 | 1 |
| Spanish NIF | =MID("TRWAGMYFPDXBNJZSQVHLCKE",MOD(--A2,23)+1,1) | 12345678 | Z |
| Belgian account | =TEXT(IF(MOD(--A2,97)=0,97,MOD(--A2,97)),"00") | 5390075470 | 34 |
Enter the payload as text. Excel 365 and Google Sheets evaluate these formulas directly; older Excel versions accept them in SUMPRODUCT without Ctrl+Shift+Enter.
What is Spreadsheet Formula Generator?
A Spreadsheet Formula Generator is a tool that produces ready-to-paste spreadsheet formulas for check digit schemes. The formulas run in Excel, Google Sheets, and LibreOffice Calc.
Spreadsheet formulas suit teams that manage identifiers in workbooks and cannot install add-ins or macros.
What are the uses of Spreadsheet Formula Generator?
There are 3 main uses of the Spreadsheet Formula Generator, including product sheets, finance workbooks, and data checks.
- Product sheets: Merchandisers add GTIN check digits in Excel.
- Finance workbooks: Accountants validate routing and account numbers.
- Data checks: Analysts flag invalid identifiers with a helper column.