Creating formula to create a check digit.. Help

[OmegaRed]

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)

 

[Fluff]

Hi & welcome to MrExcel.
How about

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
	I	J
2	27700001	277000014
3	27700004	277000048
4	56519903	565199031
All

Cell Formulas
Range	Formula
J2	=ChkDigit(I2)

 

[Eric W]

You can do it with formulas:

	A	B	C	D	E	F	G	H	I
1	27700001	4		Number	Digit	Multiplier	Product	Adjusted Product
2	27700004	8		27700001	2	1	2	2
3	56519903	1			7	2	14	5
4					7	1	7	7
5					0	2	0	0
6					0	1	0	0
7					0	2	0	0
8					0	1	0	0
9					1	2	2
10
11							Sum:	16
12							Remainder:	6
13							Check Digit:	4

<tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula E2 =MID(TEXT($D$2,"00000000"),ROW()-1,1)+0 G2 =E2*F2 H2 =IF(G2>9,G2-9,G2) H11 =SUM(H2:H9) H12 =MOD(H11,10) H13 =10-H12 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B1 {=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)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

 

[OmegaRed]

Thank you so much it worked out great

 

[OmegaRed]

Thanks Eric.. Worked out great.. Just needed to add =Right(Formula)+10). Was getting 10 as a check digit instead of zero.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Eric W]

Yes, I wondered if that would be an issue. Good job fixing it. Glad we could help.