I'm not sure how we'd get a check digit of 10, because you change the
digits accordingly - so wouldn't it therefore produce a check digit
still in the range 0-10?


Duncan wrote:
This works good aiden but it wont work on the entries that result in a
check digit of 10, dont know if this could be worked around cos its
like saying run the validation again but first add to the constant, not
sure if thats possible in data validation.

I wish they wouldnt use such a stupid method for check digit! its like
reiventing the wheel, and leaving out the tyre!

ah well I have to work with this method as its what is used and I cant
change that, im really new to check digits, shame really.

Regards

Duncan


wrote:

I'm not 100% sure about the final bit of the check digit (If the check
digit value is 10, add 1 to the base number and repeat the process) but
you can use DATA VALIDATION to handle this - ignoring the final bit the
validation goes like this

=AND(LEN(DATATOCHECK)=7,MOD((MID(DATATOCHECK,1,1)* 13)+(MID(DATATOCHECK,2,1)*11)+(MID(DATATOCHECK,3,1 )*7)+(MID(DATATOCHECK,4,1)*5)+(MID(DATATOCHECK,5,1 )*3)+(MID(DATATOCHECK,6,1)*2)+RIGHT(DATATOCHECK,1) ,11)=0)

replace datatocheck with the actual cell reference - this is entered as
CUSTOM on the validation choice.

I'm ASSUMING you are locked in to the check digit mechanism, otherwise
would suggest using A as a valid check digit (base 11!)

Duncan wrote:
Hi all

I know that this is really way out of my league, I would love for
someone to come up with some code to check if a number is valid using
the check digit method I will paste below. (a 7 digit number which
really should be a 6 digit number with the 7th being the check digit)

I can just about work it out using a spreadsheet with loads of formulas
dotted about but would have a clue how to code something to check a
number according to this method.

Hope someone can help

Regards

Duncan

(see below for method)

The check digit is at the end of the number (7 digit number).

The check digit is calculated from the base number as follows: (base
number is first 6 digits)

Multiply digit 1 of the base number by 13,
digit 2 by 11,
digit 3 by 7,
digit 4 by 5,
digit 5 by 3,
digit 6 by 2

and add the results of these multiplications together

Divide the overall sum above by 11 and obtain the remainder.

Subtract the remainder from 11 to give the check digit.

If the check digit value is 11, change it to 0.
If the check digit value is 10, add 1 to the base number and repeat the
process.

Suffix the check digit to the base number to give the registration
number.

Note that with this in place, there will obviously be a 1 in 10 chance
of entering a 'valid' number which won't necessarily be correct.