Need a formula to calculate a MOD10 check digit for a string of 26 numbers.

Artkrum4th

New Member
Joined
Oct 25, 2023
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello! I have tinkered with some formulas I found on the interwebs but they are not producing the resulting digit that passes the cager's validation (this is for a fundraising mailer). I am at my wits end! I can convert alphas to numbers, so I do not need that part.

Here are the requirements:

Mod-10 Check Digit Routine


Example

Scanline: 40290350 HDS1114 12



4 0 2 9 0 3 5 0 H D S 1 1 1 4 1 2



Alphas Converted: 4 0 2 9 0 3 5 0 8 4 2 1 1 1 4 1 2

Weights: 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2

Products: 8 0 4 9 0 3 10 0 16 4 4 1 2 1 8 1 4

Sum of Digits: 8 + 0 + 4 + 9 + 0 + 3 +1+0 +0+ 1+6+ 4 + 4 + 1 + 2 + 1 + 8 + 1 + 4 = 57

Modulus 10: 57 % 10 = 7 (modulus returns the remainder of 57 divided by 10)

Complement Remainder: 10 - 7 = 3 (subtract the remainder 7 from 10)

Check Digit: 3
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Here is an example of the cager's corrected check digit for one of the records:

# 1 Moody
Scanline w/o Check Digit
0​
0​
3​
J​
0​
0​
0​
0​
0​
1​
8​
Q​
Y​
B​
L​
A​
A​
4​
2​
3​
T​
F​
H​
6​
H​
G​
CD is pre-printed as 7​
Convert Alphas
0​
0​
3​
1​
0​
0​
0​
0​
0​
1​
8​
8​
8​
2​
3​
1​
1​
4​
2​
3​
3​
6​
8​
6​
8​
7​
Assign Weight Pattern (2 1 2 1)
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
2​
1​
Multiply
0​
0​
6​
1​
0​
0​
0​
0​
0​
1​
16​
8​
16​
2​
6​
1​
2​
4​
4​
3​
6​
6​
16​
6​
16​
7​
If product was 10 or above, sum the two digits
0​
0​
6​
1​
0​
0​
0​
0​
0​
1​
7​
8​
7​
2​
6​
1​
2​
4​
4​
3​
6​
6​
7​
6​
7​
7​
Subtotal
91​
Divide by 10
/ 10​
Quotient
9.10​
Subtract the remainder from 10
10 - 1​
Check Digit
9​
 
Upvote 0
On the row: If product was 10 or above, sum the two digits

How are you getting 7 from the numbers: 8, 2 and 16
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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