W
Walter Briscoe
The data of the machine readable zone consists of two rows of 44
characters each. The only characters used are A-Z, 0-9 and the filler
character <. [snip]
The format of the second row is:
positions chars meaning [snip]
22-27 num expiration date of passport (YYMMDD)
28 num check digit over digits 22-27 [snip]
The check digit calculation is as follows: each position is assigned a
value; for the digits 0 to 9 this is the value of the digits, for the
letters A to Z this is 10 to 35, for the filler < this is 0. The value
of each position is then multiplied by its weight; the weight of the
first position is 7, of the second it is 3, and of the third it is 1,
and after that the weights repeat 7, 3, 1, etcetera. All values are
added together and the remainder of the final value divided by 10 is
the check digit.
I have successfully coded a calculation of the various check digits.
I do not like what I have as it uses data for the weights, rather than
embedding data in the formulae.
e.g. Suppose a passport's expiration date is 10 November 2012.
This can be represented by 1,2,1,1,1,0 in A1:A6.
I want to put the check digit in A7.
I put the weights 7,3,1,7,3,1 in B1:B6
I put this formula in A7 (Set this up as an array formula)
=MOD(SUMPRODUCT(A1:F1,A2:F2),10)
(This formula is a slight simplification as it ignores character
encoding. cf. the URL if that interests you)
I could use the array constant {7,3,1,7,3,1}.
I would like to use the repeat {7,3,1} in some fashion.
I have shown the check digit calculation for expiration date which is a
6 character field.
I would like a method which deals with fields of other lengths.
e.g. There might be a calculation for the check digit on the 7 character
field expiration date and its check digit.
The longest field consists of 14 characters.
(I am hoping to extend my understanding of worksheet functions.