Modulus 11 excel formulae to generate 7th digit

[reggie k]

Hi Guys, is there anyone know modulus 11 excel formulae to generate 7th digit.

Please see below formulae that i tried and it didn't work.

=MOD(SUM(B1:G1*B2:G2),11)=0

Below is Actual calculation.
Example: find the check digit for the number 036532
[TABLE="width: 361"]
<tbody>[TR]
[TD="width: 15%"][/TD]
[TD="width: 17%"]3
[/TD]
[TD="width: 17%"]6
[/TD]
[TD="width: 17%"]5
[/TD]
[TD="width: 17%"]3
[/TD]
[TD="width: 18%"]2
[/TD]
[/TR]
[TR]
[TD="width: 15%"]x7
[/TD]
[TD="width: 17%"]x6
[/TD]
[TD="width: 17%"]x5
[/TD]
[TD="width: 17%"]x4
[/TD]
[TD="width: 17%"]x3
[/TD]
[TD="width: 18%"]x2
[/TD]
[/TR]
[TR]
[TD="width: 15%"][/TD]
[TD="width: 17%"]18
[/TD]
[TD="width: 17%"]30
[/TD]
[TD="width: 17%"]20
[/TD]
[TD="width: 17%"]9
[/TD]
[TD="width: 18%"]4
[/TD]
[/TR]
</tbody>[/TABLE]
0 + 18 + 30 + 20 + 9 + 4 = 81
81/11 = 7 remainder 4
11 - 4 = 7
7 is therefore the check digit.

 

[Joe4]

{=11-MOD(SUM(B1:G1*B2:G2),11)}

Note this is an array formula. It means that the squiggly brackets are not really there, rather you need to enter the formula by pressing CTRL-SHIFT-ENTER.

 

[reggie k]

Hi Joe4, thanks for the reply. But i didn't get what you mean by enter the formula by pressing CTRL-SHIFT-ENTER?

 

[Joe4]

It really is as straightforward as it sounds.
After you type in formula, do NOT just hit ENTER. Hit CTRL-SHIFT-ENTER (all three at the same time).

See this for an explanantion on array formulas: https://support.office.com/en-us/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

 

[reggie k]

Hi JOE4, thanks it worked.

=11-MOD(SUM(B1:G1*B2:G2),11)

if 7th digit is greater then 9, i want it should be printed as X, any idea?

 

[Joe4]

Since that formula that figures that out is an Array formula, I don't think we can do it all in one Excel formula.
If the formula above is placed in cell H2, then you can add this formula in another cell:

=IF(H2>9,H2,"X")

Note that I have done check digit programming before, and because of the complexity, I usually create a User Defined Function in VBA to do the whole calculation and return the final result.
When you have long or complex functionality, I find that usually works better and is easier to maintain.

 

[reggie k]

That's wonderful, thanks mate

 

[Joe4]

You are welcome.