Machine-readable passport calculations
Walter Briscoe
for calculating numbers in machine-readable passports.
> 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
Ron Rosenfeld
For digit strings, where the entire string is in a single cell (i.e. not individual digits in a range of cells), you can use this formula:
=MOD(SUMPRODUCT(MID(String,ROW(
INDIRECT("1:"&LEN(String))),1)*MID(
REPT("731",5),ROW(INDIRECT("1:"&LEN(
String))),1)),10)
Ron Rosenfeld
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)
----------------------------------------
To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
Walter Briscoe
2011 08:44:08 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld <r...@nospam.net> writes
>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.
>----------------------------------------
>
>To **array-enter** a formula, after entering
>the formula into the cell or formula bar, hold down
><ctrl><shift> while hitting <enter>. If you did this
>correctly, Excel will place braces {...} around the formula.
--
Walter Briscoe
Ron Rosenfeld
>Wow! I found your first formula was ineffective and, on thinking about
>it, realised I needed to array-enter it.
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 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.
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)))
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)
>
>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.
FIRST you have to conceptualize what you are going to do. Then I start from the inside and work outwards.
Walter Briscoe
2011 07:22:27 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld <r...@nospam.net> writes
>On Wed, 16 Feb 2011 07:24:31 +0000, Walter Briscoe
><wbri...@nospam.demon.co.uk> wrote:
>
>>Wow! I found your first formula was ineffective and, on thinking about
>>it, realised I needed to array-enter it.
>
>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.
>
>
>
>>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.
>
>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.
>
>>
>>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.
>
>FIRST you have to conceptualize what you are going to do. Then I start
>from the inside and work outwards.
Thanks, again!!!
--
Walter Briscoe
Ron Rosenfeld
>Thank you. I have not solved my original problem - one character per
>cell - but have learned a lot. My ignorance probably astounds you.
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.
And we were all ignorant at one point in time.
Walter Briscoe
2011 18:16:24 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld <r...@nospam.net> writes
I quote from <http://en.wikipedia.org/wiki/Machine-readable_passport>
> 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
Ron Rosenfeld
Sometimes it helps to use the Evaluate Formula wizard to see what is going on, but here's a start.
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?
Walter Briscoe
2011 02:49:18 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld <r...@nospam.net> writes
[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
Ron Rosenfeld
>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
>Did you decide not to show a multi-cell array formula?
I don't understand this question.
Walter Briscoe
2011 11:53:42 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld <r...@nospam.net> writes
>On Thu, 17 Feb 2011 13:41:18 +0000, Walter Briscoe
><wbri...@nospam.demon.co.uk> wrote:
>
>>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
>
>>Did you decide not to show a multi-cell array formula?
>
>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
Ron Rosenfeld
>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.
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.
Walter Briscoe
2011 06:23:28 in microsoft.public.excel.worksheet.functions, Ron
Rosenfeld <r...@nospam.net> writes
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?
--
Walter Briscoe
Ron Rosenfeld
>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?
This is the first time you've mentioned having data in any cells other than A1:A6 with a check digit in A7.
1. How do you put a passport number, which is 9 digits, into A1:A8?
2. Since your numbers are fixed in position, merely substitute the appropriate range reference for String.
So, assuming you will really be putting your passport number in A1:A9, try:
A10:
=MOD(SUM(IF(CODE($A$1:$A$9)<60,$A$1:$A$9,IF($A$1:$A$9>="A",CODE($A$1:$A$9)-55,0))*(MID(REPT("731",13),ROW(INDIRECT("1:"&COUNTA($A$1:$A$9))),1))),10)
A20:
=MOD(SUM(IF(CODE($A$14:$A$19)<60,$A$14:$A$19,IF($A$14:$A$19>="A",CODE($A$14:$A$19)-55,0))*(MID(REPT("731",13),ROW(INDIRECT("1:"&COUNTA($A$14:$A$19))),1))),10)