Machine-readable passport calculations

[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.

 

[Walter Briscoe]

In message <[email protected]> of Tue, 15 Feb
2011 08:44:08 in microsoft.public.excel.worksheet.functions, Ron

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.

Actually, if you are going to include all of the check digit fields,
the longest field might be 39. To account for all that, as well as
handle letters, and again, with the characters being "checked" in a
single cell NAME'd String, you can try this formula:

This formula must be **array-entered**:

=MOD(SUM(IF(CODE(MID(String,ROW(INDIRECT(
"1:"&LEN(String))),1))<60,MID(String,ROW(
INDIRECT("1:"&LEN(String))),1),IF(MID(String,ROW(
INDIRECT("1:"&LEN(String))),1)>="A",CODE(MID(
String,ROW(INDIRECT("1:"&LEN(String))),1))-55,0))* ^^^
MID(REPT("731",13),ROW(INDIRECT("1:"&LEN(String))),1)),10)

Wow! I found your first formula was ineffective and, on thinking about
it, realised I needed to array-enter it. I have tried your new formula.
I changed String to B34. When I pasted the formula in, I found LEN after
"A" was highlit and I was given a "The formula you typed contained an
error.". "I have an error in my formula" help advised "You can enter, or
nest, no more than seven levels of functions within a function.".
That LEN is at a nesting level of 9 according to my calculations.

I must apologise. I did not say I use Excel 2003. I assume that
constraint is relaxed in later Excel versions.

How do you construct such a formula?
I assume you start from the innermost function and move out to a
reasonable level and get that working and then add levels in turn.
I will try that. In the meantime. I advise what I have found so far.

 

[Walter Briscoe]

In message <[email protected]> of Wed, 16 Feb
2011 07:22:27 in microsoft.public.excel.worksheet.functions, Ron

That is odd. On my machine (Excel 2007)

=MOD(SUMPRODUCT(MID(String,ROW(
INDIRECT("1:"&LEN(String))),1)*MID(
REPT("731",13),ROW(INDIRECT("1:"&LEN(
String))),1)),10)

can be normally entered. Are you sure you entered it exactly as shown?

I wasn't entering it as shown as I know little of named references and
did not appreciate you were using one. I have now reminded myself how to
enter one and that formula gets the right answer for a couple of sets of
data. That gives me 99% confidence.

Yes, later versions allow more levels of nesting.

To reduce the nesting, try this:

If you use a range reference instead of the Named Reference "String",
try entering it as an absolute reference initially.

Define Name: LenString
refers to: =ROW(INDIRECT("1:"&LEN(String)))

I did not know how to do this. I found nothing in Excel Help. I Googled
and found <http://www.exceltip.com/st/Saving_a_Frequently_Used_Formula/N
umeric_Value_in_the_Define_Name_Dialog_Box/116.html>. I then typed
Ctrl+F3 to open the Define Name dialog. I completed that dialog and your
formula below worked.

Then use this **array** formula:

=MOD(SUM(IF(CODE(MID(String,LenString,1))<60,
MID(String,LenString,1),IF(MID(String,LenString,1)>="A",
CODE(MID(String,LenString,1))-55,0))*MID(REPT("731",13),LenString,1)),10)

That works too!

I had played with your previous work after my last posting and found
that reducing the nesting by 1 resulted in a working formula - I had
used B34 in place of referring to B34 as String.
Your technique of nesting names seems to dramatically increase the
complexity of expressions which Excel can handle.

Thank you. I have not solved my original problem - one character per
cell - but have learned a lot. My ignorance probably astounds you.

FIRST you have to conceptualize what you are going to do. Then I start
from the inside and work outwards.

Thanks, again!!!

 

[Walter Briscoe]

In message <[email protected]> of Wed, 16 Feb
2011 18:16:24 in microsoft.public.excel.worksheet.functions, Ron

You can solve this problem "one character per cell". I just thought it
would be simpler to enter the entire string in a single cell, instead
of splitting it up. If you need more assistance with that, post back.

The format of the second row is:
positions chars meaning
1-9 alpha+num passport number
10 num check digit over digits 1-9
11-13 alpha nationality (ISO 3166-1 alpha-3 code with modifications)
14-19 num date of birth (YYMMDD)
20 num check digit over digits 14-19
21 alpha sex (M, F or < for male, female or unspecified)
22-27 num expiration date of passport (YYMMDD)
28 num check digit over digits 22-27
29-42 alpha+num personal number (may be used by the issuing country as it desires)
43 num check digit over digits 29-42 (may be < if all characters are <)
44 num check digit over digits 1-10, 14-20, and 22-43

Initially, I used a sheet with rows such as this starting in row 20:

A/1 B/2 C/3 D/4 E/5 F/6 G/7 H/I
20 Position 22 23 24 25 26 27 28
21 Expiration date + check digit 4 5 0 1 2 3 9
22 Weight 7 3 1 7 3 1
23 Value 28 15 0 7 6 3

Cells(23,2) = Cells(21,2) * Cells(22,2) through to Cells(27,2)
Cells(21,28) = =Sum(B23:G23)

I then realised I did not need row 23 if I recoded
Cells(21,28) = =Sumproduct(B21:G21,B22:G22)

I wanted to eliminate the need for the Weight row by embedding it in
data.

I took the several field calculations and put them in line so I had

A/1 B/2 C/3 ... AS/45
3 Passport line 1 2 44
4 Encoding
5 Weight

(Encoding copies down the value above for numeric fields)

I wanted to eliminate the Encoding and Weight lines by embedding what
they do in formulae.

And we were all ignorant at one point in time.

I do not understand the iteration in formulae such as
=SUM(IF(CODE(MID(String,ROW(INDIRECT("1:"&LEN(String))),1))<60,MID(Strin
g,ROW(INDIRECT("1:"&LEN(String))),1),IF(MID(String,ROW(INDIRECT("1:"&LEN
(String))),1)>="A",CODE(MID(String,ROW(INDIRECT("1:"&LEN(String))),1))-5
5,0))*MID(REPT("731",13),ROW(INDIRECT("1:"&LEN(String))),1))

Assuming LEN(String) is 6,
MID(String,ROW(INDIRECT("1:"&LEN(String))),1) is equivalent to
MID(String,ROW(INDIRECT("1:6")),1)

Iteration seems to be done over 1:6 in the array function. How?
(I do not understand that use of INDIRECT. =INDIRECT("1") and
=ROW(INDIRECT("1")) both return #REF! as ordinary and array
calculations.)

I started out looking for data in one line without supplementary data.
The named references seem to be "logically" equivalent to that
supplementary data. OTOH, there is a considerable increase in
sophistication in using named references.

Please bear with me.

 

[Walter Briscoe]

In message <[email protected]> of Thu, 17 Feb
2011 02:49:18 in microsoft.public.excel.worksheet.functions, Ron

On Thu, 17 Feb 2011 07:11:24 +0000, Walter Briscoe
[snip]

Please bear with me.

Sometimes it helps to use the Evaluate Formula wizard to see what is
going on, but here's a start.

That helps enormously.
I found it at Tools/Formula Auditing/Evaluate Formula.

You have the correct concept in your initial tries where you are using
SUMPRODUCT.

SUMPRODUCT can multiply one array by another. In your case, you are
setting up the array elements as individual cells. In my example, I
have developed arrays within the formula, using the row(indirect...
construct.

However, when you tried to figure it out, INDIRECT must use valid range
references. "1" is not a valid range reference, so indirect("1")
returns an error, as you have noted.

However, 1:1 IS a valid range reference. It refers to the entire first
row. So indirect("1:1") will not give an error.

and likewise 1:6 is a valid range reference, referring to the entire
first six rows.

So then, row(indirect("1:6")) --> {1,2,3,4,5,6} and, when used as the
start_num parameter for the MID function, will return a sequential
array of the string elements.

Does that make it more clear?

It does. I kick myself for not starting with row(indirect("1:6")).
That results in 1 in the cell containing the formula.
[As does row(indirect("6:1")). I expected an error.]
Can 1, 2, 3, 4, 5, 6 be loaded in a cell array with a similar call?

Did you decide not to show a multi-cell array formula?

 

[Walter Briscoe]

In message <[email protected]> of Thu, 17 Feb
2011 11:53:42 in microsoft.public.excel.worksheet.functions, Ron

It does. I kick myself for not starting with row(indirect("1:6")).
That results in 1 in the cell containing the formula.
[As does row(indirect("6:1")). I expected an error.]
Can 1, 2, 3, 4, 5, 6 be loaded in a cell array with a similar call?

Actually, although you see just a 1 with the formula
=row(indirect("1:6")) in the cell, there is really a horizontal array
in there that is {1;2;3;4;5;6}

You can see this by placing your cursor in the formula bar, and then
striking <F9>.

Or you can see the individual componets by using the INDEX function

That is what I was looking for.
e.g. =INDEX(ROW(INDIRECT("1:6")),3) is 3

I don't understand this question.

You gave formulae which catered for input in strings, where a single
cell contains a sequence of characters e.g. "3<1415927".
I want one character per cell.

 

[Walter Briscoe]

In message <[email protected]> of Fri, 18 Feb
2011 06:23:28 in microsoft.public.excel.worksheet.functions, Ron

Using the same algorithm, it is relatively simple to convert to using
one character per cell. Data input may be more difficult that way but,
for a vertical array starting in A1, as you initially posted, and
having a maximum of 39 characters (as per the specs for possible check
digits) do the following:

1. Define Name: String
Refers To: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$39))

(This is a dynamic range, the size of which will expand/contract
depending on how many entries you have in the range. There must be NO
other entries in the range, and the entries must be consecutive with no
intervening blanks).

2. Use this formula -- **Array-Entered**

=MOD(SUM(IF(CODE(String)<60,String,IF(String>="A",CODE(String)-55,0))*(
MID(REPT("731",13),ROW(INDIRECT("1:"&COUNTA(String))),1))),10)

It is basically the same formula except String will return an array of
values as written, rather than having to produce an Array with the MID
function.

I am sorry. I REALLY must be obtuse. I can't see you to apply those 2
formula to data.
I put my 44 characters in A1:A44
A1:A8 is the passport number and A9 is the checkdigit
A14:A19 is the holder's date of birth and A20 is the checkdigit.
I don't see how A1:A8 and A14:A19 are communicated to your formulae to
evaluate in parallel. i.e. What do I put in A9 and A20?

 

[azedine1]

HI GUYS I HAVE A RELLY PUZZLING ISSUE , I WOULD LIKE TO GET SOME HELP : I HAVE TRYING TO CALCULATE THE LAST CHECK DIGIT ON THE SECOND LINE OF MRZ OF A FRENCH PASSPORT OR FRENCH ID CARD AND I ALWAYS GET IT WRONG CAN ANY ONE EXPLAIN TO ME HOW TO calculate THE LAST KEY DIGIT PLEASE