Creating formula to create a check digit.. Help

O

OmegaRed

New Member
Joined
Mar 7, 2019
Messages
3
Need a hand please. I'm not great with formulas in Excel.. Searched the forum with no luck with the help I need. Below describes what I need to create a check digit. Looking for a formula to do this in Excel.

Thanks for looking and help.


The calculation is as follows:

digit 1 + digit 3 + digit 5 + digit 7 +
digit 2 * 2 (if greater than 9, subtract 9) +
digit 4 * 2 (if greater than 9, subtract 9) +
digit 6 * 2 (if greater than 9, subtract 9) +
digit 8 * 2 (if greater than 9, subtract 9)

The sum from this calculation is divided by 10. The remainder from this division is then subtracted from ten. This remainder is the check digit.

In the examples from above:

277000014

Digit 1 - 2 Total 2
Digit 2 - 7 times 2 = 14, since greater than 9, subtract 9 giving 5 Total 7
Digit 3 - 7 Total 14
Digit 4 - 0 times 2 = 0 Total 14
Digit 5 - 0 Total 14
Digit 6 - 0 times 2 = 0 Total 14
Digit 7 - 0 Total 14
Digit 8 - 1 times 2 = 2, not greater than 9 so it’s 2 Total 16
Check Digit is 4, which is 10 - (remainder of 16 / 10)

277000048

Digit 1 - 2 Total 2
Digit 2 - 7 times 2 = 14, since greater than 9, subtract 9 giving 5 Total 7
Digit 3 - 7 Total 14
Digit 4 - 0 times 2 = 0 Total 14
Digit 5 - 0 Total 14
Digit 6 - 0 times 2 = 0 Total 14
Digit 7 - 0 Total 14
Digit 8 - 4 times 2 = 8, not greater than 9 so it’s 8 Total 22
Check Digit is 8, which is 10 - (remainder of 22 / 10)

565199031

Digit 1 - 5 Total 5
Digit 2 - 6 times 2 = 12, since greater than 9, subtract 9 giving 3 Total 8
Digit 3 - 5 Total 13
Digit 4 - 1 times 2 = 2, not greater than 9 so it’s 2 Total 15
Digit 5 - 9 Total 24
Digit 6 - 9 times 2 = 18, since greater than 9, subtract 9 giving 9 Total 33
Digit 7 - 0 Total 33
Digit 8 - 3 times 2 = 6, not greater than 9 so it’s 6 Total 39
Check Digit is 1, which is 10 - (remainder of 39 / 10)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
Code: 
Function ChkDigit(Vlu As Long) As Long
   Dim i As Long, x As Long
   
   For i = 1 To 7 Step 2
      x = x + Mid(Vlu, i, 1) * 1
   Next i
   For i = 2 To 8 Step 2
      If Mid(Vlu, i, 1) * 2 > 9 Then
         x = x + (Mid(Vlu, i, 1) * 2 - 9)
      Else
         x = x + Mid(Vlu, i, 1) * 2
      End If
   Next i
   ChkDigit = Vlu * 10 + 10 - (x Mod 10)
End Function
Used like


Excel 2013/2016
IJ
227700001277000014
327700004277000048
456519903565199031
All
Cell Formulas
RangeFormula
J2=ChkDigit(I2)
 
Upvote 0
You can do it with formulas:

ABCDEFGHI
NumberDigitMultiplierProductAdjusted Product
7
Sum:
Remainder:
Check Digit:

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]27700001[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]27700004[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]27700001[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]56519903[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=MID(TEXT($D$2,"00000000"),ROW()-1,1)+0[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=E2*F2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(G2>9,G2-9,G2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H11[/TH]
[TD="align: left"]=SUM(H2:H9)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H12[/TH]
[TD="align: left"]=MOD(H11,10)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H13[/TH]
[TD="align: left"]=10-H12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]{=10-MOD(SUM(MID(TEXT(A1,"00000000"),{1,2,3,4,5,6,7,8},1)*{1,2,1,2,1,2,1,2}-IF(MID(TEXT(A1,"00000000"),{9,2,9,4,9,6,9,8},1)>"4",9)),10)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]






The array formula in B1 can do all the calculations in one cell (don't forget the Control+Shift+Enter). However, something like this is often easier to visualize, check, and maintain if you use a series of simpler formulas. If you put the number in the D2 cell, then the E formulas split out the digits. The F column has the multipliers, the G column has the products, the H column subtracts 9 if needed, then H11 calculates the sum, H12 gets the remainder, and H13 the final answer. Every formula along the way is pretty simple.

Hope this helps.
 
Upvote 0
Thanks Eric.. Worked out great.. Just needed to add =Right(Formula)+10). Was getting 10 as a check digit instead of zero.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Yes, I wondered if that would be an issue. Good job fixing it. Glad we could help. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Telephone numbers are Hard!
2
Replies
10
Views
340
dowtym
D
B
  • Question Question
Closest Match in a Range
Replies
1
Views
382
Rijnsent
Rijnsent
M
  • Question Question
VBA changing values in multiple columns - where it should only be changing a single column
Replies
1
Views
371
Akuini
Akuini
A
  • Question Question
Need a formula to calculate a MOD10 check digit for a string of 26 numbers.
Replies
2
Views
1K
Georgiboy
Georgiboy
serge
  • Question Question
Returning one single numer.
2
Replies
12
Views
641
Eric W
Eric W

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top