Help: Modulus 11 Prime Method

[tmvirusred]

I am new to this check digit thing, and cannot figure out how to create a formula to find the check digit. I've been looking through this forum and just don't quite understand it.

I need to generate a check digit to be appended to a four digit number. The number has to be entered in a single 4 digit number, and not a 4 individual number. For example:

1. Four digit number: 3462 (or could even start with one or more zeros)
2. Weight: 2357
3.The check digit would be 7:
3 x 2 = 6
4 x 3 = 12
6 x 5 = 30
2 x 7 = 14 Total = 62 ----> 62/11 = 5 with remainder 7
4. And some how add on the 7 check digit to 3462 to get 34627 in another cell

 

[ChrisOswald]

Since i didn't know if the weight is constant or not, I'll give you two versions.

Version one

Excel Workbook
	A	B	C
1	CheckNbr	WeightNbr	Result
2	3462	2357	34627
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C2	=--(A2&MOD(MID(A2,1,1)*MID(B2,1,1)+MID(A2,2,1)*MID(B2,2,1)+MID(A2,3,1)*MID(B2,3,1)+MID(A2,4,1)*MID(B2,4,1),11))

Weight number 'Hard coded'

Excel Workbook
	A	B
1	CheckNbr	Result
2	3462	34627
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=--(A2&MOD(MID(A2,1,1)*2+MID(A2,2,1)*3+MID(A2,3,1)*5+MID(A2,4,1)*7,11))

I'm sure there's some nifty array formula for this (to allow for numbers that aren't four digits), but I'm drawing a blank at the moment.

If you want to return the result as text, remove the -- from the begining of the formulas.

 

[tmvirusred]

Thank you, very much. I am having problems on one 4 digit number. It begins with "00" and I am getting #VAULE!.

There is that word again "TEXT", what does TEXT mean if you don't mind me asking?

And yes the weight is constant, I'm sorry that I did not specify that early.

 

[xenou]

Numbers don't *really* begin with 00 so its formatting you are seeing (probably) or numbers stored as text.

Try:
MID(Text(A2,"0000"),1,1)
Or:
VALUE(MID(Text(A2,"0000"),1,1))
Or:
VALUE(Mid(A2,1,1))

Note: look under Excel help for an explanation of the TEXT() function. I've not really looked closely at this so consider these as suggestions or clues rather than a solution.

 

[tmvirusred]

COOL!!! Thank you.

Is there such thing as like a reversing formula where you can check to see if the check digit at the end is correct?

Such as in my previous example, 34627, is there a way to check to see if 7 is the correct check digit? While keeping the digits in the same cell and not individually separating the digits into different cells.